{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:40:27.826134Z",
     "iopub.status.busy": "2023-11-07T03:40:27.825828Z",
     "iopub.status.idle": "2023-11-07T03:40:28.519235Z",
     "shell.execute_reply": "2023-11-07T03:40:28.518618Z",
     "shell.execute_reply.started": "2023-11-07T03:40:27.826099Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from tqdm import tqdm\n",
    "import os\n",
    "import time\n",
    "import gc\n",
    "from sklearn.model_selection import StratifiedKFold, KFold\n",
    "from sklearn.metrics import roc_auc_score,f1_score\n",
    "from sklearn.decomposition import TruncatedSVD\n",
    "from sklearn.feature_extraction.text import CountVectorizer, TfidfVectorizer\n",
    "from scipy import sparse"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1 数据读取"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:40:28.520535Z",
     "iopub.status.busy": "2023-11-07T03:40:28.520335Z",
     "iopub.status.idle": "2023-11-07T03:40:28.855908Z",
     "shell.execute_reply": "2023-11-07T03:40:28.855313Z",
     "shell.execute_reply.started": "2023-11-07T03:40:28.520510Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "EBANK_CSTLOG_train = pd.read_csv('../contest/train/EBANK_CSTLOG_QZ.csv') #网银金融性流水表\n",
    "CUST_FA_SUM_train = pd.read_csv('../contest/train/CUST_FA_SUM_QZ.csv')   #客户金融资产信息表\n",
    "NATURE_CUST_train = pd.read_csv('../contest/train/NATURE_CUST_QZ.csv')   #自然属性表\n",
    "TARGET_train = pd.read_csv('../contest/train/TARGET_QZ.csv')   #目标客户表\n",
    "DP_CUST_SUM_train =pd.read_csv('../contest/train/DP_CUST_SUM_QZ.csv')  #客户定活期存款信息表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:40:28.857359Z",
     "iopub.status.busy": "2023-11-07T03:40:28.857156Z",
     "iopub.status.idle": "2023-11-07T03:40:28.913442Z",
     "shell.execute_reply": "2023-11-07T03:40:28.912867Z",
     "shell.execute_reply.started": "2023-11-07T03:40:28.857335Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "EBANK_CSTLOG_B = pd.read_csv('../contest/B/EBANK_CSTLOG_QZ_B.csv') #网银金融性流水表\n",
    "CUST_FA_SUM_B = pd.read_csv('../contest/B/CUST_FA_SUM_QZ_B.csv')   #客户金融资产信息表\n",
    "NATURE_CUST_B = pd.read_csv('../contest/B/NATURE_CUST_QZ_B.csv')   #自然属性表\n",
    "TARGET_B = pd.read_csv('../contest/B/TARGET_QZ_B.csv')   #目标客户表\n",
    "DP_CUST_SUM_B=pd.read_csv('../contest/B/DP_CUST_SUM_QZ_B.csv')  #客户定活期存款信息表"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2 数据探索"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:40:28.914718Z",
     "iopub.status.busy": "2023-11-07T03:40:28.914522Z",
     "iopub.status.idle": "2023-11-07T03:40:29.246858Z",
     "shell.execute_reply": "2023-11-07T03:40:29.246263Z",
     "shell.execute_reply.started": "2023-11-07T03:40:28.914694Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#定义描述性函数\n",
    "import seaborn as sns\n",
    "def stat_df(df):\n",
    "    stats = []\n",
    "    for col in df.columns:\n",
    "        stats.append((col, df[col].nunique(),df[col].isnull().sum()*100/df.shape[0],\n",
    "                     df[col].value_counts(normalize=True,dropna=False).values[0]*100,df[col].dtype))\n",
    "    stats_df = pd.DataFrame(stats, columns=['特征','唯一数数量','缺失值占比','最多数占比','类型'])\n",
    "    stats_df.sort_values('缺失值占比',ascending=False)\n",
    "    return stats_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.1 目标客户表"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "训练集总客户数：37050  数据日期： 19940715\n",
    "\n",
    "验证集总客户数：A_5024/B_5089   数据日期： A_19940814   /B_19940913"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-23T07:09:30.064996Z",
     "iopub.status.busy": "2023-10-23T07:09:30.064702Z",
     "iopub.status.idle": "2023-10-23T07:09:30.069052Z",
     "shell.execute_reply": "2023-10-23T07:09:30.068395Z",
     "shell.execute_reply.started": "2023-10-23T07:09:30.064968Z"
    },
    "tags": []
   },
   "source": [
    "## 2.2 网银金融性流水性表\n",
    "训练集：网银金融性流水性表，4月份的有17天，5月份的有25天，6月份的有14天\n",
    "\n",
    "A_测试集：网银金融性流水性表，5月份的有18天，6月份的有23天，7月份的有14天\n",
    "\n",
    "B_测试集：网银金融性流水表，6月的有18天，7月的有25天，8月的有13天"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-23T07:19:27.608889Z",
     "iopub.status.busy": "2023-10-23T07:19:27.608592Z",
     "iopub.status.idle": "2023-10-23T07:19:27.611845Z",
     "shell.execute_reply": "2023-10-23T07:19:27.611216Z",
     "shell.execute_reply.started": "2023-10-23T07:19:27.608859Z"
    },
    "tags": []
   },
   "source": [
    "## 2.3 客户金融资产表"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "训练集：数据日期-19940614\n",
    "\n",
    "A_测试集：数据日期-19940714\n",
    "\n",
    "b_测试集：数据日期-19940813\n",
    "\n",
    "由于此表日期唯一，无法进行滑窗，可考虑进行特征衍生"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-23T07:32:20.299772Z",
     "iopub.status.busy": "2023-10-23T07:32:20.299477Z",
     "iopub.status.idle": "2023-10-23T07:32:20.302684Z",
     "shell.execute_reply": "2023-10-23T07:32:20.302135Z",
     "shell.execute_reply.started": "2023-10-23T07:32:20.299742Z"
    },
    "tags": []
   },
   "source": [
    "## 2.4 自然属性表"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "客户性别和客户等级可进行编码，无数据日期\n",
    "\n",
    "训练集：NTRL_CUST_SEX_CD 有少许缺失\n",
    "\n",
    "测试集：暂无缺失"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.5 客户定活期存款信息表"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "训练集测试集只有一个日期\n",
    "\n",
    "训练集客户数：36985\n",
    "\n",
    "测试集客户数：5019"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-23T07:37:34.419063Z",
     "iopub.status.busy": "2023-10-23T07:37:34.418759Z",
     "iopub.status.idle": "2023-10-23T07:37:34.421957Z",
     "shell.execute_reply": "2023-10-23T07:37:34.421416Z",
     "shell.execute_reply.started": "2023-10-23T07:37:34.419036Z"
    },
    "tags": []
   },
   "source": [
    "# 3 特征加工"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:40:29.247959Z",
     "iopub.status.busy": "2023-11-07T03:40:29.247761Z",
     "iopub.status.idle": "2023-11-07T03:40:29.263009Z",
     "shell.execute_reply": "2023-11-07T03:40:29.262508Z",
     "shell.execute_reply.started": "2023-11-07T03:40:29.247934Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#训练集和测试集拼表处理\n",
    "target_data=pd.concat([TARGET_train, TARGET_B], axis = 0).reset_index(drop = True)\n",
    "#日期处理\n",
    "target_data['DATA_DAT'] = pd.to_datetime(target_data['DATA_DAT'], format='%Y%m%d')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.1 自然信息表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:40:29.264025Z",
     "iopub.status.busy": "2023-11-07T03:40:29.263843Z",
     "iopub.status.idle": "2023-11-07T03:40:29.267645Z",
     "shell.execute_reply": "2023-11-07T03:40:29.267165Z",
     "shell.execute_reply.started": "2023-11-07T03:40:29.264003Z"
    }
   },
   "outputs": [],
   "source": [
    "#定义one_hot编码函数\n",
    "def one_hot(df, cate_cols):\n",
    "    df_dummies = pd.get_dummies(df.loc[:,[cate_cols]])\n",
    "    return pd.concat([df,df_dummies],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:40:29.268872Z",
     "iopub.status.busy": "2023-11-07T03:40:29.268697Z",
     "iopub.status.idle": "2023-11-07T03:40:29.298872Z",
     "shell.execute_reply": "2023-11-07T03:40:29.298384Z",
     "shell.execute_reply.started": "2023-11-07T03:40:29.268850Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#训练集和测试集合并处理\n",
    "nature_cust_data = pd.concat([NATURE_CUST_train, NATURE_CUST_B], axis = 0).reset_index(drop = True) \n",
    "# 对【NTRL_RANK_CD】进行one_hot 编码，并删除原特征\n",
    "nature_cust_data=one_hot(nature_cust_data,'NTRL_RANK_CD')\n",
    "del nature_cust_data['NTRL_RANK_CD']\n",
    "#对性别进行编码\n",
    "sex_dict = {'A': 0, 'B': 1}\n",
    "nature_cust_data['NTRL_CUST_SEX_CD'] = nature_cust_data['NTRL_CUST_SEX_CD'].map(sex_dict)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 保存"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:40:29.299947Z",
     "iopub.status.busy": "2023-11-07T03:40:29.299770Z",
     "iopub.status.idle": "2023-11-07T03:40:29.320811Z",
     "shell.execute_reply": "2023-11-07T03:40:29.320334Z",
     "shell.execute_reply.started": "2023-11-07T03:40:29.299925Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pickle\n",
    "\n",
    "# 指定要保存的文件名\n",
    "file_name = \"./feature/B_9_nature.pkl\"\n",
    "\n",
    "# 使用pickle.dump()将特征矩阵保存为二进制文件\n",
    "with open(file_name, 'wb') as file:\n",
    "    pickle.dump(nature_cust_data, file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.2 金融资产表(金融资产表和定活期)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:40:29.478903Z",
     "iopub.status.busy": "2023-11-07T03:40:29.478683Z",
     "iopub.status.idle": "2023-11-07T03:40:29.607865Z",
     "shell.execute_reply": "2023-11-07T03:40:29.607297Z",
     "shell.execute_reply.started": "2023-11-07T03:40:29.478878Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#训练集和测试集合并处理\n",
    "cust_fa_sum_data =  pd.concat([CUST_FA_SUM_train, CUST_FA_SUM_B], axis = 0).reset_index(drop = True) \n",
    "dp_cust_sum_data = pd.concat([DP_CUST_SUM_train,DP_CUST_SUM_B],axis = 0).reset_index(drop = True)\n",
    "\n",
    "#和目标表拼接处理\n",
    "asset_data=target_data.merge(cust_fa_sum_data, on = 'CUST_NO', how = 'left')\n",
    "asset_data=asset_data.merge(dp_cust_sum_data, on = 'CUST_NO', how = 'left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 特征衍生"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:40:29.685730Z",
     "iopub.status.busy": "2023-11-07T03:40:29.685502Z",
     "iopub.status.idle": "2023-11-07T03:40:29.794276Z",
     "shell.execute_reply": "2023-11-07T03:40:29.793687Z",
     "shell.execute_reply.started": "2023-11-07T03:40:29.685705Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "\n",
    "#特征衍生7：当日aum分别与年/季/月日均做对比\n",
    "asset_data['aum_y']=(asset_data['DAY_AUM_BAL']-asset_data['YAVER_AUM_BAL'])/asset_data['DAY_AUM_BAL']\n",
    "asset_data['aum_s']=(asset_data['DAY_AUM_BAL']-asset_data['SAVER_AUM_BAL'])/asset_data['DAY_AUM_BAL']\n",
    "asset_data['aum_m']=(asset_data['DAY_AUM_BAL']-asset_data['YAVER_AUM_BAL'])/asset_data['DAY_AUM_BAL']\n",
    "\n",
    "#特征衍生8：当日活期存款分别与年/季/月日均做对比\n",
    "asset_data['DPSA_y']=(asset_data['DPSA_BAL']-asset_data['YAVER_DPSA_BAL'])/asset_data['DPSA_BAL']\n",
    "asset_data['DPSA_s']=(asset_data['DPSA_BAL']-asset_data['SAVER_DPSA_BAL'])/asset_data['DPSA_BAL']\n",
    "asset_data['DPSA_m']=(asset_data['DPSA_BAL']-asset_data['MAVER_DPSA_BAL'])/asset_data['DPSA_BAL']\n",
    "\n",
    "\n",
    "#特征衍生1：AUM/金融资产（包括当日/月日均/季日均/年日均）\n",
    "asset_data['DAY_AUM_FA']=asset_data['DAY_AUM_BAL']/(asset_data['DAY_FA_BAL']+ 0.00000000001)\n",
    "asset_data['MAVER_AUM_FA']=asset_data['MAVER_AUM_BAL']/(asset_data['MAVER_FA_BAL']+ 0.00000000001)\n",
    "asset_data['SAVER_AUM_FA']=asset_data['SAVER_AUM_BAL']/(asset_data['SAVER_FA_BAL']+ 0.00000000001)\n",
    "asset_data['YAVER_AUM_FA']=asset_data['YAVER_AUM_BAL']/(asset_data['YAVER_FA_BAL']+ 0.00000000001)\n",
    "\n",
    "#特征衍生2：活期存款/aum（包括当日/月日均/季日均/年日均）\n",
    "asset_data['DAY_huoqi_aum']=asset_data['DPSA_BAL']/(asset_data['DAY_AUM_BAL']+ 0.00000000001)\n",
    "asset_data['MAVER_huoqi_aum']=asset_data['MAVER_DPSA_BAL']/(asset_data['MAVER_AUM_BAL']+ 0.00000000001)\n",
    "asset_data['SAVER_huoqi_aum']=asset_data['SAVER_DPSA_BAL']/(asset_data['SAVER_AUM_BAL']+ 0.00000000001)\n",
    "asset_data['YAVER_huoqi_aum']=asset_data['YAVER_DPSA_BAL']/(asset_data['YAVER_AUM_BAL']+ 0.00000000001)\n",
    "\n",
    "#特征衍生3：定期存款/aum（包括当日/月日均/季日均/年日均）\n",
    "asset_data['DAY_dingqi_aum']=asset_data['TD_BAL']/(asset_data['DAY_AUM_BAL']+ 0.00000000001)\n",
    "asset_data['MAVER_dingqi_aum']=asset_data['MAVER_TD_BAL']/(asset_data['MAVER_AUM_BAL']+ 0.00000000001)\n",
    "asset_data['SAVER_dingqi_aum']=asset_data['SAVER_TD_BAL']/(asset_data['SAVER_AUM_BAL']+ 0.00000000001)\n",
    "asset_data['YAVER_dingqi_aum']=asset_data['YAVER_TD_BAL']/(asset_data['YAVER_AUM_BAL']+ 0.00000000001)\n",
    "\n",
    "#特征衍生4：金融资产/总投资（包括当日/月日均/季日均/年日均）\n",
    "asset_data['DAY_fa_touzi']=asset_data['DAY_FA_BAL']/(asset_data['TOT_IVST_BAL']+ 0.00000000001)\n",
    "asset_data['MAVER_fa_touzi']=asset_data['MAVER_FA_BAL']/(asset_data['MAVER_TOT_IVST_BAL']+ 0.00000000001)\n",
    "asset_data['SAVER_fa_touzi']=asset_data['SAVER_FA_BAL']/(asset_data['SAVER_TOT_IVST_BAL']+ 0.00000000001)\n",
    "asset_data['YAVER_fa_touzi']=asset_data['YAVER_FA_BAL']/(asset_data['YAVER_TOT_IVST_BAL']+ 0.00000000001)\n",
    "\n",
    "#特征衍生5：fa-aum(贷款)/金融资产（包括当日/月日均/季日均/年日均）\n",
    "asset_data['DAY_daikuan_fa']=(asset_data['DAY_FA_BAL']-asset_data['DAY_AUM_BAL'])/(asset_data['DAY_FA_BAL']+ 0.00000000001)\n",
    "asset_data['MAVER_daikuan_fa']=(asset_data['MAVER_FA_BAL']-asset_data['MAVER_AUM_BAL'])/(asset_data['MAVER_FA_BAL']+ 0.00000000001)\n",
    "asset_data['SAVER_daikuan_fa']=(asset_data['SAVER_FA_BAL']-asset_data['SAVER_AUM_BAL'])/(asset_data['SAVER_FA_BAL']+ 0.00000000001)\n",
    "asset_data['YAVER_daikuan_fa']=(asset_data['YAVER_FA_BAL']-asset_data['YAVER_AUM_BAL'])/(asset_data['YAVER_FA_BAL']+ 0.00000000001)\n",
    "\n",
    "#特征衍生6：活期+定期/aum（包括当日/月日均/季日均/年日均）\n",
    "asset_data['DAY_huoding_aum']=(asset_data['DPSA_BAL']+asset_data['TD_BAL'])/(asset_data['DAY_AUM_BAL']+ 0.00000000001)\n",
    "asset_data['MAVER_huoding_aum']=(asset_data['MAVER_DPSA_BAL']+asset_data['MAVER_TD_BAL'])/(asset_data['MAVER_AUM_BAL']+ 0.00000000001)\n",
    "asset_data['SAVER_huoding_aum']=(asset_data['SAVER_DPSA_BAL']+asset_data['SAVER_TD_BAL'])/(asset_data['SAVER_AUM_BAL']+ 0.00000000001)\n",
    "asset_data['YAVER_huoding_aum']=(asset_data['YAVER_DPSA_BAL']+asset_data['YAVER_TD_BAL'])/(asset_data['YAVER_AUM_BAL']+ 0.00000000001)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:40:29.885540Z",
     "iopub.status.busy": "2023-11-07T03:40:29.885306Z",
     "iopub.status.idle": "2023-11-07T03:40:29.895413Z",
     "shell.execute_reply": "2023-11-07T03:40:29.894866Z",
     "shell.execute_reply.started": "2023-11-07T03:40:29.885515Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "col=['DATA_DAT_x','CARD_NO','DATA_DAT_y','FLAG','DATA_DAT']\n",
    "for i in col:\n",
    "    del asset_data[i]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:40:30.080077Z",
     "iopub.status.busy": "2023-11-07T03:40:30.079835Z",
     "iopub.status.idle": "2023-11-07T03:40:30.085209Z",
     "shell.execute_reply": "2023-11-07T03:40:30.084731Z",
     "shell.execute_reply.started": "2023-11-07T03:40:30.080052Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(42139, 51)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "asset_data.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 保存"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:40:30.285849Z",
     "iopub.status.busy": "2023-11-07T03:40:30.285631Z",
     "iopub.status.idle": "2023-11-07T03:40:30.355263Z",
     "shell.execute_reply": "2023-11-07T03:40:30.354627Z",
     "shell.execute_reply.started": "2023-11-07T03:40:30.285825Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pickle\n",
    "\n",
    "# 指定要保存的文件名\n",
    "file_name = \"./feature/B_6&&7_asset.pkl\"\n",
    "\n",
    "# 使用pickle.dump()将特征矩阵保存为二进制文件\n",
    "with open(file_name, 'wb') as file:\n",
    "    pickle.dump(asset_data, file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.3 掌银金融性流水表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:40:30.486806Z",
     "iopub.status.busy": "2023-11-07T03:40:30.486568Z",
     "iopub.status.idle": "2023-11-07T03:40:34.613821Z",
     "shell.execute_reply": "2023-11-07T03:40:34.613197Z",
     "shell.execute_reply.started": "2023-11-07T03:40:30.486776Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "MBANK_TRNFLW_train = pd.read_csv('../contest/train/MBANK_TRNFLW_QZ.csv') #掌银金融性流水表\n",
    "MBANK_TRNFLW_B = pd.read_csv('../contest/B/MBANK_TRNFLW_QZ_B.csv') "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 日期修正"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:40:34.615236Z",
     "iopub.status.busy": "2023-11-07T03:40:34.614977Z",
     "iopub.status.idle": "2023-11-07T03:40:40.515459Z",
     "shell.execute_reply": "2023-11-07T03:40:40.514805Z",
     "shell.execute_reply.started": "2023-11-07T03:40:34.615209Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# 训练集\n",
    "MBANK_TRNFLW_train['TFT_DTE_TIME'] = pd.to_datetime(MBANK_TRNFLW_train['TFT_DTE_TIME'], format='%Y%m%d%H%M%S')\n",
    "MBANK_TRNFLW_train['TFT_DTE_year'] = MBANK_TRNFLW_train['TFT_DTE_TIME'].dt.year\n",
    "MBANK_TRNFLW_train['TFT_DTE_month'] = MBANK_TRNFLW_train['TFT_DTE_TIME'].dt.month\n",
    "MBANK_TRNFLW_train['TFT_DTE_day'] = MBANK_TRNFLW_train['TFT_DTE_TIME'].dt.day\n",
    "\n",
    "MBANK_TRNFLW_train['TFT_DTE_month'] =  MBANK_TRNFLW_train['TFT_DTE_month'].replace(4,1)\n",
    "MBANK_TRNFLW_train['TFT_DTE_month'] =  MBANK_TRNFLW_train['TFT_DTE_month'].replace(5,2)\n",
    "MBANK_TRNFLW_train['TFT_DTE_month'] =  MBANK_TRNFLW_train['TFT_DTE_month'].replace(6,3)\n",
    "\n",
    "#B测试集\n",
    "MBANK_TRNFLW_B['TFT_DTE_TIME'] = pd.to_datetime(MBANK_TRNFLW_B['TFT_DTE_TIME'], format='%Y%m%d%H%M%S')\n",
    "MBANK_TRNFLW_B['TFT_DTE_year'] = MBANK_TRNFLW_B['TFT_DTE_TIME'].dt.year\n",
    "MBANK_TRNFLW_B['TFT_DTE_month'] = MBANK_TRNFLW_B['TFT_DTE_TIME'].dt.month\n",
    "MBANK_TRNFLW_B['TFT_DTE_day'] = MBANK_TRNFLW_B['TFT_DTE_TIME'].dt.day\n",
    "\n",
    "MBANK_TRNFLW_B['TFT_DTE_month'] = MBANK_TRNFLW_B['TFT_DTE_month'].replace(6,1)\n",
    "MBANK_TRNFLW_B['TFT_DTE_month'] = MBANK_TRNFLW_B['TFT_DTE_month'].replace(7,2)\n",
    "MBANK_TRNFLW_B['TFT_DTE_month'] = MBANK_TRNFLW_B['TFT_DTE_month'].replace(8,3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:40:40.517073Z",
     "iopub.status.busy": "2023-11-07T03:40:40.516852Z",
     "iopub.status.idle": "2023-11-07T03:41:20.046237Z",
     "shell.execute_reply": "2023-11-07T03:41:20.045581Z",
     "shell.execute_reply.started": "2023-11-07T03:40:40.517032Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "MBANK_TRNFLW_data=pd.DataFrame()\n",
    "MBANK_TRNFLW_data = pd.concat([MBANK_TRNFLW_train, MBANK_TRNFLW_B], axis = 0).reset_index(drop = True)\n",
    "\n",
    "#统一客户号字段名\n",
    "MBANK_TRNFLW_data=MBANK_TRNFLW_data.rename(columns={\"TFT_CSTNO\":\"CUST_NO\"})\n",
    "#拼表\n",
    "tr_MBANK_TRNFLW=target_data.merge(MBANK_TRNFLW_data,on=\"CUST_NO\",how=\"left\")\n",
    "\n",
    "#计算时间差\n",
    "def quzheng(col1, col2):\n",
    "    return (col1-col2) \n",
    "\n",
    "\n",
    "tr_MBANK_TRNFLW[\"time_diff\"]=list(map(lambda app_time,time:quzheng(app_time,time),tr_MBANK_TRNFLW[\"DATA_DAT\"],tr_MBANK_TRNFLW[\"TFT_DTE_TIME\"]))\n",
    "tr_MBANK_TRNFLW[\"time_diff\"]=tr_MBANK_TRNFLW[\"time_diff\"].astype('timedelta64[D]')\n",
    "\n",
    "col=['DATA_DAT','TFT_DTE_TIME','TFT_DTE_year']\n",
    "for i in col:\n",
    "    del tr_MBANK_TRNFLW[i]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-24T02:22:07.606994Z",
     "iopub.status.busy": "2023-10-24T02:22:07.606700Z",
     "iopub.status.idle": "2023-10-24T02:22:07.610075Z",
     "shell.execute_reply": "2023-10-24T02:22:07.609501Z",
     "shell.execute_reply.started": "2023-10-24T02:22:07.606966Z"
    },
    "tags": []
   },
   "source": [
    "### 3.3.1 频数分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:41:20.047728Z",
     "iopub.status.busy": "2023-11-07T03:41:20.047509Z",
     "iopub.status.idle": "2023-11-07T03:41:20.054726Z",
     "shell.execute_reply": "2023-11-07T03:41:20.054064Z",
     "shell.execute_reply.started": "2023-11-07T03:41:20.047703Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Groupby\n",
    "def make_gp(df, pk, agg_col=None, agg_actions=None, agg_dict=None, extra_col=None):\n",
    "    if agg_dict is not None:\n",
    "        pass\n",
    "    elif (agg_col is not None) and (agg_actions is not None):\n",
    "        agg_dict = dict.fromkeys(agg_col)\n",
    "        for key in agg_dict:\n",
    "            agg_dict[key] = agg_actions\n",
    "    else:\n",
    "        raise ValueError(\"请输入要进行Groupby的列和操作\")\n",
    "    \n",
    "    if extra_col is not None:\n",
    "        gp = df.groupby(pk)[extra_col].agg(agg_dict)\n",
    "    else:\n",
    "        gp = df.groupby(pk).agg(agg_dict)\n",
    "    \n",
    "    gp.columns = ['_'.join(col).strip() for col in gp.columns.values]\n",
    "    gp.reset_index(inplace=True)\n",
    "    return gp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:41:20.055801Z",
     "iopub.status.busy": "2023-11-07T03:41:20.055607Z",
     "iopub.status.idle": "2023-11-07T03:41:20.059433Z",
     "shell.execute_reply": "2023-11-07T03:41:20.058918Z",
     "shell.execute_reply.started": "2023-11-07T03:41:20.055777Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "### 重新命名字段段名\n",
    "def rename_df(df, group_id,ext=\"\"):\n",
    "    new_cols = [group_id]\n",
    "    for i in df.columns:\n",
    "        if i!=group_id:\n",
    "            new_cols.append(ext+\"_\"+i)\n",
    "    return new_cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:41:20.060421Z",
     "iopub.status.busy": "2023-11-07T03:41:20.060242Z",
     "iopub.status.idle": "2023-11-07T03:43:01.284444Z",
     "shell.execute_reply": "2023-11-07T03:43:01.283892Z",
     "shell.execute_reply.started": "2023-11-07T03:41:20.060400Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(41287, 49)"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 根据time_diff统计交易频数\n",
    "tmqlw_1 = tr_MBANK_TRNFLW.copy(deep=True)\n",
    "tmqlw_1_time_diff = make_gp(tmqlw_1, ['CUST_NO', 'time_diff'], ['CUST_NO'], ['count'])\n",
    "tmqlw_1_time_diff.columns = ['CUST_NO', 'time_diff', 'times']\n",
    "tmqlw_1_time_diff_1 = make_gp(tmqlw_1_time_diff, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "tmqlw_1_time_diff_1.columns = rename_df(tmqlw_1_time_diff_1,'CUST_NO',\"trnflw_custno\")\n",
    "\n",
    "# 根据TFT_DTE_month统计交易频数\n",
    "tmqlw_1_month = make_gp(tmqlw_1, ['CUST_NO', 'TFT_DTE_month'], ['CUST_NO'], ['count'])\n",
    "tmqlw_1_month.columns = ['CUST_NO', 'TFT_DTE_month', 'montimes']\n",
    "tmqlw_1_month_1 =  make_gp(tmqlw_1_month, ['CUST_NO'], ['TFT_DTE_month', 'montimes'], ['mean', 'sum', 'median', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "tmqlw_1_month_1.columns = rename_df(tmqlw_1_month_1,'CUST_NO',\"trnflw_mon\")\n",
    "\n",
    "# 根据TFT_DTE_day统计交易频数\n",
    "tmqlw_1_day= make_gp(tmqlw_1, ['CUST_NO', 'TFT_DTE_day'], ['CUST_NO'], ['count'])\n",
    "tmqlw_1_day.columns = ['CUST_NO', 'TFT_DTE_day', 'daytimes']\n",
    "tmqlw_1_day_1 = make_gp(tmqlw_1_day, ['CUST_NO'], ['TFT_DTE_day', 'daytimes'], ['mean', 'sum', 'median', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "tmqlw_1_day_1.columns = rename_df(tmqlw_1_day_1,'CUST_NO',\"trnflw_day\")\n",
    "\n",
    "#汇总整理\n",
    "tmqlw_1_huizong = pd.DataFrame()\n",
    "tmqlw_1_huizong= tmqlw_1_time_diff_1.merge(tmqlw_1_month_1, on = 'CUST_NO', how = 'left')\n",
    "tmqlw_1_huizong= tmqlw_1_huizong.merge(tmqlw_1_day_1, on = 'CUST_NO', how = 'left')\n",
    "\n",
    "tmqlw_1_huizong.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.3.2 交易金额分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:43:01.285645Z",
     "iopub.status.busy": "2023-11-07T03:43:01.285448Z",
     "iopub.status.idle": "2023-11-07T03:44:30.905894Z",
     "shell.execute_reply": "2023-11-07T03:44:30.905387Z",
     "shell.execute_reply.started": "2023-11-07T03:43:01.285621Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(42139, 36)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#交易金额统计\n",
    "trnflw_am1 = tr_MBANK_TRNFLW.copy(deep=True)\n",
    "trnflw_am1_1 = make_gp(trnflw_am1,  ['CUST_NO'], ['TFT_TRNAMT'],['mean', 'sum', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "trnflw_am1_1.columns = rename_df(trnflw_am1_1,'CUST_NO',\"trnflw_cnoAMT\")\n",
    "\n",
    "#根据交易金额按月分组统计\n",
    "trnflw_am1_m = make_gp(trnflw_am1,  ['CUST_NO'], ['TFT_DTE_month', 'TFT_TRNAMT'],['mean', 'sum', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "trnflw_am1_m.columns = rename_df(trnflw_am1_m,'CUST_NO',\"trnflw_mAMT\")\n",
    "\n",
    "#根据交易金额按天分组统计\n",
    "trnflw_am1_d= make_gp(trnflw_am1,  ['CUST_NO'], ['TFT_DTE_day', 'TFT_TRNAMT'],['mean', 'sum', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "trnflw_am1_d.columns = rename_df(trnflw_am1_d,'CUST_NO',\"trnflw_dAMT\")\n",
    "\n",
    "#汇总整理\n",
    "tmqlw_am1_huizong = pd.DataFrame()\n",
    "tmqlw_am1_huizong= trnflw_am1_1.merge(trnflw_am1_m, on = 'CUST_NO', how = 'left')\n",
    "tmqlw_am1_huizong= tmqlw_am1_huizong.merge(trnflw_am1_d, on = 'CUST_NO', how = 'left')\n",
    "tmqlw_am1_huizong.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.3.3 交易代码分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:44:30.907898Z",
     "iopub.status.busy": "2023-11-07T03:44:30.907670Z",
     "iopub.status.idle": "2023-11-07T03:44:34.802278Z",
     "shell.execute_reply": "2023-11-07T03:44:34.801776Z",
     "shell.execute_reply.started": "2023-11-07T03:44:30.907872Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(42139, 11)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#交易代码统计\n",
    "trnflw_cod1 = tr_MBANK_TRNFLW.copy(deep=True)\n",
    "trnflw_cod1_1 = make_gp(trnflw_cod1,  ['CUST_NO'], ['TFT_STDBSNCOD'],['count', 'nunique'])\n",
    "trnflw_cod1_1.columns = rename_df(trnflw_cod1_1,'CUST_NO',\"trnflw_cod\")\n",
    "#按月交易代码统计\n",
    "trnflw_cod1_m = make_gp(trnflw_cod1,  ['CUST_NO'], ['TFT_DTE_month','TFT_STDBSNCOD'],['count', 'nunique'])\n",
    "trnflw_cod1_m.columns = rename_df(trnflw_cod1_m,'CUST_NO',\"trnflw_mcod\")\n",
    "#按天交易代码统计\n",
    "trnflw_cod1_d = make_gp(trnflw_cod1,  ['CUST_NO'], ['TFT_DTE_day','TFT_STDBSNCOD'],['count', 'nunique'])\n",
    "trnflw_cod1_d.columns = rename_df(trnflw_cod1_d,'CUST_NO',\"trnflw_dcod\")\n",
    "#汇总整理\n",
    "tmqlw_cod1_huizong = pd.DataFrame()\n",
    "tmqlw_cod1_huizong= trnflw_cod1_1.merge(trnflw_cod1_m, on = 'CUST_NO', how = 'left')\n",
    "tmqlw_cod1_huizong= tmqlw_cod1_huizong.merge(trnflw_cod1_d, on = 'CUST_NO', how = 'left')\n",
    "tmqlw_cod1_huizong.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.3.4 交易对手账号分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:44:34.803777Z",
     "iopub.status.busy": "2023-11-07T03:44:34.803567Z",
     "iopub.status.idle": "2023-11-07T03:44:39.076840Z",
     "shell.execute_reply": "2023-11-07T03:44:39.076340Z",
     "shell.execute_reply.started": "2023-11-07T03:44:34.803753Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(42139, 11)"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#交易对手账号统计\n",
    "trnflw_acc1 = tr_MBANK_TRNFLW.copy(deep=True)\n",
    "trnflw_acc1_1 = make_gp(trnflw_acc1,  ['CUST_NO'], ['TFT_CSTACC'],['count', 'nunique'])\n",
    "trnflw_acc1_1.columns = rename_df(trnflw_acc1_1,'CUST_NO',\"trnflw_acc\")\n",
    "#按月统计交易对手\n",
    "trnflw_acc1_m = make_gp(trnflw_acc1,  ['CUST_NO'], ['TFT_DTE_month','TFT_CSTACC'],['count', 'nunique'])\n",
    "trnflw_acc1_m.columns = rename_df(trnflw_acc1_m,'CUST_NO',\"trnflw_macc\")\n",
    "#按天统计交易对手\n",
    "trnflw_acc1_d = make_gp(trnflw_acc1,  ['CUST_NO'], ['TFT_DTE_day','TFT_CSTACC'],['count', 'nunique'])\n",
    "trnflw_acc1_d.columns = rename_df(trnflw_acc1_d,'CUST_NO',\"trnflw_dacc\")\n",
    "\n",
    "#汇总整理\n",
    "trnflw_acc1_huizong = pd.DataFrame()\n",
    "trnflw_acc1_huizong= trnflw_acc1_1.merge(trnflw_acc1_m, on = 'CUST_NO', how = 'left')\n",
    "trnflw_acc1_huizong= trnflw_acc1_huizong.merge(trnflw_acc1_d, on = 'CUST_NO', how = 'left')\n",
    "trnflw_acc1_huizong.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.3.5 汇总所有分组统计的结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:44:39.077956Z",
     "iopub.status.busy": "2023-11-07T03:44:39.077759Z",
     "iopub.status.idle": "2023-11-07T03:44:39.586559Z",
     "shell.execute_reply": "2023-11-07T03:44:39.586064Z",
     "shell.execute_reply.started": "2023-11-07T03:44:39.077932Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(41287, 104)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trnflw_feature=pd.DataFrame(MBANK_TRNFLW_data[\"CUST_NO\"].drop_duplicates())\n",
    "trnflw_feature=trnflw_feature.merge(tmqlw_1_huizong, on = 'CUST_NO', how = 'left')\n",
    "trnflw_feature=trnflw_feature.merge(tmqlw_am1_huizong, on = 'CUST_NO', how = 'left')  \n",
    "trnflw_feature=trnflw_feature.merge(tmqlw_cod1_huizong, on = 'CUST_NO', how = 'left')\n",
    "trnflw_feature=trnflw_feature.merge(trnflw_acc1_huizong, on = 'CUST_NO', how = 'left') \n",
    "trnflw_feature.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.3.6 交易频数滑窗统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:44:39.587697Z",
     "iopub.status.busy": "2023-11-07T03:44:39.587502Z",
     "iopub.status.idle": "2023-11-07T03:46:30.392689Z",
     "shell.execute_reply": "2023-11-07T03:46:30.391329Z",
     "shell.execute_reply.started": "2023-11-07T03:44:39.587673Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(39702, 65)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#时间滑窗交易频数统计：近2周/近4周/近6周/近8周的频数、间隔及与全量之比例\n",
    "trnflw_2 = tmqlw_1_time_diff.copy(deep=True) \n",
    "\n",
    "trnflw_2_2 = trnflw_2.loc[trnflw_2['time_diff']>42]\n",
    "trnflw_2_2 = make_gp(trnflw_2_2, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "trnflw_2_2.columns = ['CUST_NO'] + [f +'_trnflw'+'_last14' for f in trnflw_2_2.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "trnflw_2_4 = trnflw_2.loc[trnflw_2['time_diff']>28]\n",
    "trnflw_2_4 = make_gp(trnflw_2_4, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "trnflw_2_4.columns = ['CUST_NO'] + [f +'_trnflw'+ '_last28' for f in trnflw_2_4.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "trnflw_2_6 = trnflw_2.loc[trnflw_2['time_diff']>14]\n",
    "trnflw_2_6 = make_gp(trnflw_2_6, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "trnflw_2_6.columns = ['CUST_NO'] + [f +'_trnflw'+ '_last42' for f in trnflw_2_6.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "trnflw_2_8 = trnflw_2.loc[trnflw_2['time_diff']>0]\n",
    "trnflw_2_8 = make_gp(trnflw_2_8, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "trnflw_2_8.columns = ['CUST_NO'] + [f +'_trnflw'+ '_last56' for f in trnflw_2_8.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "#汇总整理\n",
    "trnflw_2_huizong=pd.DataFrame()\n",
    "trnflw_2_huizong=trnflw_2_2.merge(trnflw_2_4, on = 'CUST_NO', how = 'left')\n",
    "trnflw_2_huizong=trnflw_2_huizong.merge(trnflw_2_6, on = 'CUST_NO', how = 'left')\n",
    "trnflw_2_huizong=trnflw_2_huizong.merge(trnflw_2_8, on = 'CUST_NO', how = 'left')\n",
    "for d in [14, 28,42,56]:\n",
    "        trnflw_2_huizong[f'times_trnflw_rate_last{d}'] = trnflw_2_huizong[f'times_sum_trnflw_last{d}'] / tmqlw_1_huizong[f'trnflw_custno_times_sum']\n",
    "        trnflw_2_huizong[f'time_diff_trnflw_rate_last{d}'] = trnflw_2_huizong[f'time_diff_sum_trnflw_last{d}'] / tmqlw_1_huizong[f'trnflw_custno_time_diff_sum']\n",
    "\n",
    "trnflw_2_huizong.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.3.7 交易金额滑窗统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:46:30.393985Z",
     "iopub.status.busy": "2023-11-07T03:46:30.393784Z",
     "iopub.status.idle": "2023-11-07T03:46:34.653756Z",
     "shell.execute_reply": "2023-11-07T03:46:34.653192Z",
     "shell.execute_reply.started": "2023-11-07T03:46:30.393959Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(39702, 17)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#时间滑窗交易金额统计：：近2周/近4周/近6周/近8周的金额与与交易总金额之比\n",
    "trnflw_3 = tr_MBANK_TRNFLW.copy(deep=True)\n",
    "trnflw_3.sort_values(by = ['CUST_NO', 'time_diff']).reset_index(drop = True)\n",
    "\n",
    "trnflw_3_2 = make_gp(trnflw_3[(trnflw_3['time_diff']>42)], ['CUST_NO',], ['TFT_TRNAMT',], ['mean', 'sum', 'max', 'min'])\n",
    "trnflw_3_2.columns = ['CUST_NO'] + [f + '_trnflwam'+'_last14' for f in trnflw_3_2.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "trnflw_3_4 = make_gp(trnflw_3[(trnflw_3['time_diff']>28)], ['CUST_NO',], ['TFT_TRNAMT',], ['mean', 'sum', 'max', 'min'])\n",
    "trnflw_3_4.columns = ['CUST_NO'] + [f + '_trnflwam'+'_last28' for f in trnflw_3_4.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "trnflw_3_6 = make_gp(trnflw_3[(trnflw_3['time_diff']>14)], ['CUST_NO',], ['TFT_TRNAMT',], ['mean', 'sum', 'max', 'min'])\n",
    "trnflw_3_6.columns = ['CUST_NO'] + [f + '_trnflwam'+ '_last42' for f in trnflw_3_6.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "trnflw_3_8 = make_gp(trnflw_3[(trnflw_3['time_diff']>0)], ['CUST_NO',], ['TFT_TRNAMT',], ['mean', 'sum', 'max', 'min'])\n",
    "trnflw_3_8.columns = ['CUST_NO'] + [f + '_trnflwam' + '_last56' for f in trnflw_3_8.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "#汇总整理\n",
    "trnflw_3_huizong=pd.DataFrame()\n",
    "trnflw_3_huizong=trnflw_3_2.merge(trnflw_3_4, on = 'CUST_NO', how = 'left')\n",
    "trnflw_3_huizong=trnflw_3_huizong.merge(trnflw_3_6, on = 'CUST_NO', how = 'left')\n",
    "trnflw_3_huizong=trnflw_3_huizong.merge(trnflw_3_8, on = 'CUST_NO', how = 'left')\n",
    "trnflw_3_huizong.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:46:34.654871Z",
     "iopub.status.busy": "2023-11-07T03:46:34.654674Z",
     "iopub.status.idle": "2023-11-07T03:46:34.765461Z",
     "shell.execute_reply": "2023-11-07T03:46:34.764873Z",
     "shell.execute_reply.started": "2023-11-07T03:46:34.654847Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "###滑窗后的交易金额占比：（总——分）/分\n",
    "for col2 in ['TFT_TRNAMT', ]:\n",
    "    trnflw_3_huizong[f'{col2}_dr_sum_last28'] = (trnflw_am1_1[f'trnflw_cnoAMT_{col2}_sum']- trnflw_3_huizong[f'{col2}_sum_trnflwam_last28'])/trnflw_3_huizong[f'{col2}_sum_trnflwam_last28']\n",
    "    trnflw_3_huizong[f'{col2}_dr_mean_last28'] = (trnflw_am1_1[f'trnflw_cnoAMT_{col2}_mean']- trnflw_3_huizong[f'{col2}_mean_trnflwam_last28'])/trnflw_3_huizong[f'{col2}_mean_trnflwam_last28']\n",
    "    trnflw_3_huizong[f'{col2}_dr_max_last28']= (trnflw_am1_1[f'trnflw_cnoAMT_{col2}_max']- trnflw_3_huizong[f'{col2}_max_trnflwam_last28'])/trnflw_3_huizong[f'{col2}_max_trnflwam_last28']                                                                                               \n",
    "    trnflw_3_huizong[f'{col2}_dr_min_last28']= (trnflw_am1_1[f'trnflw_cnoAMT_{col2}_min']- trnflw_3_huizong[f'{col2}_min_trnflwam_last28'])/trnflw_3_huizong[f'{col2}_min_trnflwam_last28']                                                                                                \n",
    "    \n",
    "    trnflw_3_huizong[f'{col2}_dr_sum_last28'] = (trnflw_am1_1[f'trnflw_cnoAMT_{col2}_sum']- trnflw_3_huizong[f'{col2}_sum_trnflwam_last28'])/trnflw_3_huizong[f'{col2}_sum_trnflwam_last28']\n",
    "    trnflw_3_huizong[f'{col2}_dr_mean_last28'] = (trnflw_am1_1[f'trnflw_cnoAMT_{col2}_mean']- trnflw_3_huizong[f'{col2}_mean_trnflwam_last28'])/trnflw_3_huizong[f'{col2}_mean_trnflwam_last28']\n",
    "    trnflw_3_huizong[f'{col2}_dr_max_last28']= (trnflw_am1_1[f'trnflw_cnoAMT_{col2}_max']- trnflw_3_huizong[f'{col2}_max_trnflwam_last28'])/trnflw_3_huizong[f'{col2}_max_trnflwam_last28']                                                                                         \n",
    "    trnflw_3_huizong[f'{col2}_dr_min_last28']= (trnflw_am1_1[f'trnflw_cnoAMT_{col2}_min']- trnflw_3_huizong[f'{col2}_min_trnflwam_last28'])/trnflw_3_huizong[f'{col2}_min_trnflwam_last28']\n",
    "    \n",
    "    trnflw_3_huizong[f'{col2}_dr_sum_last42'] = (trnflw_am1_1[f'trnflw_cnoAMT_{col2}_sum']- trnflw_3_huizong[f'{col2}_sum_trnflwam_last42'])/trnflw_3_huizong[f'{col2}_sum_trnflwam_last42']\n",
    "    trnflw_3_huizong[f'{col2}_dr_mean_last42'] = (trnflw_am1_1[f'trnflw_cnoAMT_{col2}_mean']- trnflw_3_huizong[f'{col2}_mean_trnflwam_last42'])/trnflw_3_huizong[f'{col2}_mean_trnflwam_last42']\n",
    "    trnflw_3_huizong[f'{col2}_dr_max_last42']= (trnflw_am1_1[f'trnflw_cnoAMT_{col2}_max']- trnflw_3_huizong[f'{col2}_max_trnflwam_last42'])/trnflw_3_huizong[f'{col2}_max_trnflwam_last42']                                                                                                    \n",
    "    trnflw_3_huizong[f'{col2}_dr_min_last42']= (trnflw_am1_1[f'trnflw_cnoAMT_{col2}_min']- trnflw_3_huizong[f'{col2}_min_trnflwam_last42'])/trnflw_3_huizong[f'{col2}_min_trnflwam_last42']\n",
    "    \n",
    "    trnflw_3_huizong[f'{col2}_dr_sum_last56'] = (trnflw_am1_1[f'trnflw_cnoAMT_{col2}_sum']- trnflw_3_huizong[f'{col2}_sum_trnflwam_last56'])/trnflw_3_huizong[f'{col2}_sum_trnflwam_last56']\n",
    "    trnflw_3_huizong[f'{col2}_dr_mean_last56'] = (trnflw_am1_1[f'trnflw_cnoAMT_{col2}_mean']- trnflw_3_huizong[f'{col2}_mean_trnflwam_last56'])/trnflw_3_huizong[f'{col2}_mean_trnflwam_last56']\n",
    "    trnflw_3_huizong[f'{col2}_dr_max_last56']= (trnflw_am1_1[f'trnflw_cnoAMT_{col2}_max']- trnflw_3_huizong[f'{col2}_max_trnflwam_last56'])/trnflw_3_huizong[f'{col2}_max_trnflwam_last56']                                                                                             \n",
    "    trnflw_3_huizong[f'{col2}_dr_min_last56']= (trnflw_am1_1[f'trnflw_cnoAMT_{col2}_min']- trnflw_3_huizong[f'{col2}_min_trnflwam_last56'])/trnflw_3_huizong[f'{col2}_min_trnflwam_last56']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:46:34.766531Z",
     "iopub.status.busy": "2023-11-07T03:46:34.766341Z",
     "iopub.status.idle": "2023-11-07T03:46:34.770170Z",
     "shell.execute_reply": "2023-11-07T03:46:34.769718Z",
     "shell.execute_reply.started": "2023-11-07T03:46:34.766508Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(39702, 29)"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trnflw_3_huizong.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3.3.8 汇总所有的滑窗结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:46:34.771089Z",
     "iopub.status.busy": "2023-11-07T03:46:34.770911Z",
     "iopub.status.idle": "2023-11-07T03:46:34.774375Z",
     "shell.execute_reply": "2023-11-07T03:46:34.773940Z",
     "shell.execute_reply.started": "2023-11-07T03:46:34.771067Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(41287, 104)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trnflw_feature.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:46:34.775232Z",
     "iopub.status.busy": "2023-11-07T03:46:34.775069Z",
     "iopub.status.idle": "2023-11-07T03:46:35.155150Z",
     "shell.execute_reply": "2023-11-07T03:46:35.154651Z",
     "shell.execute_reply.started": "2023-11-07T03:46:34.775212Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(41287, 196)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trnflw_feature=trnflw_feature.merge(trnflw_2_huizong, on = 'CUST_NO', how = 'left')\n",
    "trnflw_feature=trnflw_feature.merge(trnflw_3_huizong, on = 'CUST_NO', how = 'left') \n",
    "trnflw_feature.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3.3.9 保存trnflw_feature特征结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:46:35.156255Z",
     "iopub.status.busy": "2023-11-07T03:46:35.156058Z",
     "iopub.status.idle": "2023-11-07T03:46:35.347054Z",
     "shell.execute_reply": "2023-11-07T03:46:35.346408Z",
     "shell.execute_reply.started": "2023-11-07T03:46:35.156231Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pickle\n",
    "\n",
    "# 指定要保存的文件名\n",
    "file_name = \"./feature/B_1_trnflw.pkl\"\n",
    "\n",
    "# 使用pickle.dump()将特征矩阵保存为二进制文件\n",
    "with open(file_name, 'wb') as file:\n",
    "    pickle.dump(trnflw_feature, file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.4 掌银非金融流水表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:46:35.348216Z",
     "iopub.status.busy": "2023-11-07T03:46:35.348003Z",
     "iopub.status.idle": "2023-11-07T03:46:44.193276Z",
     "shell.execute_reply": "2023-11-07T03:46:44.192676Z",
     "shell.execute_reply.started": "2023-11-07T03:46:35.348192Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "MBANK_QRYTRNFLW_train=pd.read_csv('../contest/train/MBANK_QRYTRNFLW_QZ.csv')  #掌银非金融流水表\n",
    "MBANK_QRYTRNFLW_B  =pd.read_csv('../contest/B/MBANK_QRYTRNFLW_QZ_B.csv') "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 日期修正"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:46:44.194435Z",
     "iopub.status.busy": "2023-11-07T03:46:44.194240Z",
     "iopub.status.idle": "2023-11-07T03:47:18.041377Z",
     "shell.execute_reply": "2023-11-07T03:47:18.040782Z",
     "shell.execute_reply.started": "2023-11-07T03:46:44.194411Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# 训练集\n",
    "MBANK_QRYTRNFLW_train['TFT_DTE_TIME'] = pd.to_datetime(MBANK_QRYTRNFLW_train['TFT_DTE_TIME'], format='%Y%m%d%H%M%S')\n",
    "MBANK_QRYTRNFLW_train['TFT_DTE_year'] = MBANK_QRYTRNFLW_train['TFT_DTE_TIME'].dt.year\n",
    "MBANK_QRYTRNFLW_train['TFT_DTE_month'] = MBANK_QRYTRNFLW_train['TFT_DTE_TIME'].dt.month\n",
    "MBANK_QRYTRNFLW_train['TFT_DTE_day'] = MBANK_QRYTRNFLW_train['TFT_DTE_TIME'].dt.day\n",
    "\n",
    "MBANK_QRYTRNFLW_train['TFT_DTE_month'] =  MBANK_QRYTRNFLW_train['TFT_DTE_month'].replace(4,1)\n",
    "MBANK_QRYTRNFLW_train['TFT_DTE_month'] =  MBANK_QRYTRNFLW_train['TFT_DTE_month'].replace(5,2)\n",
    "MBANK_QRYTRNFLW_train['TFT_DTE_month'] =  MBANK_QRYTRNFLW_train['TFT_DTE_month'].replace(6,3)\n",
    "\n",
    "#B测试集\n",
    "MBANK_QRYTRNFLW_B['TFT_DTE_TIME'] = pd.to_datetime(MBANK_QRYTRNFLW_B['TFT_DTE_TIME'], format='%Y%m%d%H%M%S')\n",
    "MBANK_QRYTRNFLW_B['TFT_DTE_year'] = MBANK_QRYTRNFLW_B['TFT_DTE_TIME'].dt.year\n",
    "MBANK_QRYTRNFLW_B['TFT_DTE_month'] = MBANK_QRYTRNFLW_B['TFT_DTE_TIME'].dt.month\n",
    "MBANK_QRYTRNFLW_B['TFT_DTE_day'] = MBANK_QRYTRNFLW_B['TFT_DTE_TIME'].dt.day\n",
    "\n",
    "MBANK_QRYTRNFLW_B['TFT_DTE_month'] = MBANK_QRYTRNFLW_B['TFT_DTE_month'].replace(6,1)\n",
    "MBANK_QRYTRNFLW_B['TFT_DTE_month'] = MBANK_QRYTRNFLW_B['TFT_DTE_month'].replace(7,2)\n",
    "MBANK_QRYTRNFLW_B['TFT_DTE_month'] = MBANK_QRYTRNFLW_B['TFT_DTE_month'].replace(8,3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:47:18.042533Z",
     "iopub.status.busy": "2023-11-07T03:47:18.042336Z",
     "iopub.status.idle": "2023-11-07T03:51:06.564458Z",
     "shell.execute_reply": "2023-11-07T03:51:06.563860Z",
     "shell.execute_reply.started": "2023-11-07T03:47:18.042508Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "MBANK_QRYTRNFLW_data=pd.DataFrame()\n",
    "MBANK_QRYTRNFLW_data = pd.concat([MBANK_QRYTRNFLW_train, MBANK_QRYTRNFLW_B], axis = 0).reset_index(drop = True)\n",
    "#统一客户号字段名\n",
    "MBANK_QRYTRNFLW_data=MBANK_QRYTRNFLW_data.rename(columns={\"TFT_CSTNO\":\"CUST_NO\"})\n",
    "#拼表\n",
    "tr_MBANK_QRYTRNFLW=target_data.merge(MBANK_QRYTRNFLW_data,on=\"CUST_NO\",how=\"left\")\n",
    "tr_MBANK_QRYTRNFLW[\"time_diff\"]=list(map(lambda app_time,time:quzheng(app_time,time),tr_MBANK_QRYTRNFLW[\"DATA_DAT\"],tr_MBANK_QRYTRNFLW[\"TFT_DTE_TIME\"]))\n",
    "tr_MBANK_QRYTRNFLW[\"time_diff\"]=tr_MBANK_QRYTRNFLW[\"time_diff\"].astype('timedelta64[D]')\n",
    "tr_MBANK_QRYTRNFLW=tr_MBANK_QRYTRNFLW[tr_MBANK_QRYTRNFLW[\"time_diff\"]>=0]\n",
    "col=['DATA_DAT','TFT_DTE_TIME','TFT_DTE_year']\n",
    "for i in col:\n",
    "    del tr_MBANK_QRYTRNFLW[i]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.4.1 频数分组统计"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 根据time_diff统计交易频数/分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:51:06.565697Z",
     "iopub.status.busy": "2023-11-07T03:51:06.565480Z",
     "iopub.status.idle": "2023-11-07T03:52:41.007000Z",
     "shell.execute_reply": "2023-11-07T03:52:41.006497Z",
     "shell.execute_reply.started": "2023-11-07T03:51:06.565672Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(41945, 49)"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 根据time_diff统计交易频数\n",
    "QRYTRNFLW_1 = tr_MBANK_QRYTRNFLW.copy(deep=True)\n",
    "QRYTRNFLW_1_time_diff = make_gp(QRYTRNFLW_1, ['CUST_NO', 'time_diff'], ['CUST_NO'], ['count'])\n",
    "QRYTRNFLW_1_time_diff.columns = ['CUST_NO', 'time_diff', 'times']\n",
    "QRYTRNFLW_1_time_diff_1 = make_gp(QRYTRNFLW_1_time_diff, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "QRYTRNFLW_1_time_diff_1.columns = rename_df(QRYTRNFLW_1_time_diff_1,'CUST_NO',\"QRYTRNFLW\")\n",
    "\n",
    "# 根据TFT_DTE_month统计交易频数\n",
    "QRYTRNFLW_1_month = make_gp(QRYTRNFLW_1, ['CUST_NO', 'TFT_DTE_month'], ['CUST_NO'], ['count'])\n",
    "QRYTRNFLW_1_month.columns = ['CUST_NO', 'TFT_DTE_month', 'montimes']\n",
    "QRYTRNFLW_1_month_1 =  make_gp(QRYTRNFLW_1_month, ['CUST_NO'], ['TFT_DTE_month', 'montimes'], ['mean', 'sum', 'median', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "QRYTRNFLW_1_month_1.columns = rename_df(QRYTRNFLW_1_month_1,'CUST_NO',\"QRYTRNFLW_m\")\n",
    "\n",
    "# 根据TFT_DTE_day统计交易频数\n",
    "QRYTRNFLW_1_day= make_gp(QRYTRNFLW_1, ['CUST_NO', 'TFT_DTE_day'], ['CUST_NO'], ['count'])\n",
    "QRYTRNFLW_1_day.columns = ['CUST_NO', 'TFT_DTE_day', 'daytimes']\n",
    "QRYTRNFLW_1_day_1 = make_gp(QRYTRNFLW_1_day, ['CUST_NO'], ['TFT_DTE_day', 'daytimes'], ['mean', 'sum', 'median', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "QRYTRNFLW_1_day_1.columns = rename_df(QRYTRNFLW_1_day_1,'CUST_NO',\"QRYTRNFLW_d\")\n",
    "\n",
    "#汇总整理\n",
    "QRYTRNFLW_1_huizong = pd.DataFrame()\n",
    "QRYTRNFLW_1_huizong= QRYTRNFLW_1_time_diff_1.merge(QRYTRNFLW_1_month_1, on = 'CUST_NO', how = 'left')\n",
    "QRYTRNFLW_1_huizong= QRYTRNFLW_1_huizong.merge(QRYTRNFLW_1_day_1, on = 'CUST_NO', how = 'left')  \n",
    "QRYTRNFLW_1_huizong.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.4.2 交易代码分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:52:41.008155Z",
     "iopub.status.busy": "2023-11-07T03:52:41.007917Z",
     "iopub.status.idle": "2023-11-07T03:53:01.918753Z",
     "shell.execute_reply": "2023-11-07T03:53:01.918252Z",
     "shell.execute_reply.started": "2023-11-07T03:52:41.008131Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(41945, 11)"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#交易代码统计\n",
    "QRYTRNFLW_cod1 = tr_MBANK_QRYTRNFLW.copy(deep=True)\n",
    "QRYTRNFLW_cod1_1 = make_gp(QRYTRNFLW_cod1,  ['CUST_NO'], ['TFT_STDBSNCOD'],['count', 'nunique'])\n",
    "QRYTRNFLW_cod1_1.columns = rename_df(QRYTRNFLW_cod1_1,'CUST_NO',\"QRYTRNFLW_cod\")\n",
    "#按月交易代码统计\n",
    "QRYTRNFLW_cod1_m = make_gp(QRYTRNFLW_cod1,  ['CUST_NO'], ['TFT_DTE_month','TFT_STDBSNCOD'],['count', 'nunique'])\n",
    "QRYTRNFLW_cod1_m.columns = rename_df(QRYTRNFLW_cod1_m,'CUST_NO',\"QRYTRNFLW_mcod\")\n",
    "#按天交易代码统计\n",
    "QRYTRNFLW_cod1_d = make_gp(QRYTRNFLW_cod1,  ['CUST_NO'], ['TFT_DTE_day','TFT_STDBSNCOD'],['count', 'nunique'])\n",
    "QRYTRNFLW_cod1_d.columns = rename_df(QRYTRNFLW_cod1_d,'CUST_NO',\"QRYTRNFLW_dcod\")\n",
    "\n",
    "#汇总整理\n",
    "QRYTRNFLW_cod1_huizong = pd.DataFrame()\n",
    "QRYTRNFLW_cod1_huizong  =QRYTRNFLW_cod1_1.merge(QRYTRNFLW_cod1_m, on = 'CUST_NO', how = 'left')\n",
    "QRYTRNFLW_cod1_huizong  =QRYTRNFLW_cod1_huizong.merge(QRYTRNFLW_cod1_d, on = 'CUST_NO', how = 'left')\n",
    "QRYTRNFLW_cod1_huizong.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.4.3 汇总所有分组统计的结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:53:01.919824Z",
     "iopub.status.busy": "2023-11-07T03:53:01.919635Z",
     "iopub.status.idle": "2023-11-07T03:53:02.909877Z",
     "shell.execute_reply": "2023-11-07T03:53:02.909378Z",
     "shell.execute_reply.started": "2023-11-07T03:53:01.919800Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(41945, 59)"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "QRYTRNFLW_feature=pd.DataFrame(MBANK_QRYTRNFLW_data[\"CUST_NO\"].drop_duplicates())\n",
    "QRYTRNFLW_feature = QRYTRNFLW_feature.merge(QRYTRNFLW_1_huizong, on = 'CUST_NO', how = 'left')\n",
    "QRYTRNFLW_feature = QRYTRNFLW_feature.merge(QRYTRNFLW_cod1_huizong, on = 'CUST_NO', how = 'left')\n",
    "QRYTRNFLW_feature.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.4.4 交易频数滑窗统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:53:02.910959Z",
     "iopub.status.busy": "2023-11-07T03:53:02.910770Z",
     "iopub.status.idle": "2023-11-07T03:54:57.547422Z",
     "shell.execute_reply": "2023-11-07T03:54:57.546768Z",
     "shell.execute_reply.started": "2023-11-07T03:53:02.910936Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#时间滑窗交易频数统计：近2周/近4周/近6周/近8周的频数、间隔及与全量之比例\n",
    "QRYTRNFLW_2 = QRYTRNFLW_1_time_diff.copy(deep=True) \n",
    "\n",
    "QRYTRNFLW_2_2 = QRYTRNFLW_2.loc[QRYTRNFLW_2['time_diff']>42]\n",
    "QRYTRNFLW_2_2 = make_gp(QRYTRNFLW_2_2, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "QRYTRNFLW_2_2.columns = ['CUST_NO'] + [f +'_QRYTRNFLW'+ '_last14' for f in QRYTRNFLW_2_2.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "QRYTRNFLW_2_4 = QRYTRNFLW_2.loc[QRYTRNFLW_2['time_diff']>28]\n",
    "QRYTRNFLW_2_4 = make_gp(QRYTRNFLW_2_4, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "QRYTRNFLW_2_4.columns = ['CUST_NO'] + [f +'_QRYTRNFLW'+ '_last28' for f in QRYTRNFLW_2_4.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "QRYTRNFLW_2_6 = QRYTRNFLW_2.loc[QRYTRNFLW_2['time_diff']>14]\n",
    "QRYTRNFLW_2_6 = make_gp(QRYTRNFLW_2_6, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "QRYTRNFLW_2_6.columns = ['CUST_NO'] + [f +'_QRYTRNFLW'+ '_last42' for f in QRYTRNFLW_2_6.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "QRYTRNFLW_2_8 = QRYTRNFLW_2.loc[QRYTRNFLW_2['time_diff']>0]\n",
    "QRYTRNFLW_2_8 = make_gp(QRYTRNFLW_2_8, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "QRYTRNFLW_2_8.columns = ['CUST_NO'] + [f +'_QRYTRNFLW'+ '_last56' for f in QRYTRNFLW_2_8.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "#汇总整理\n",
    "QRYTRNFLW_2_huizong=pd.DataFrame()\n",
    "QRYTRNFLW_2_huizong=QRYTRNFLW_2_2.merge(QRYTRNFLW_2_4, on = 'CUST_NO', how = 'left')\n",
    "QRYTRNFLW_2_huizong=QRYTRNFLW_2_huizong.merge(QRYTRNFLW_2_6, on = 'CUST_NO', how = 'left')\n",
    "QRYTRNFLW_2_huizong=QRYTRNFLW_2_huizong.merge(QRYTRNFLW_2_8, on = 'CUST_NO', how = 'left')\n",
    "\n",
    "##作比\n",
    "for d in [14, 28,42,56]:\n",
    "        QRYTRNFLW_2_huizong[f'times_QRYTRNFLW_rate_last{d}'] = QRYTRNFLW_2_huizong[f'times_sum_QRYTRNFLW_last{d}'] / QRYTRNFLW_1_huizong[f'QRYTRNFLW_times_sum']\n",
    "        QRYTRNFLW_2_huizong[f'time_QRYTRNFLW_diff_rate_last{d}'] = QRYTRNFLW_2_huizong[f'time_diff_sum_QRYTRNFLW_last{d}'] / QRYTRNFLW_1_huizong[f'QRYTRNFLW_time_diff_sum']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.4.5  汇总所有结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:54:57.548508Z",
     "iopub.status.busy": "2023-11-07T03:54:57.548317Z",
     "iopub.status.idle": "2023-11-07T03:54:57.770767Z",
     "shell.execute_reply": "2023-11-07T03:54:57.770156Z",
     "shell.execute_reply.started": "2023-11-07T03:54:57.548484Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "QRYTRNFLW_feature=QRYTRNFLW_feature.merge(QRYTRNFLW_2_huizong, on = 'CUST_NO', how = 'left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.4.6 保存QRYTRNFLW_feature 特征"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:54:57.771906Z",
     "iopub.status.busy": "2023-11-07T03:54:57.771711Z",
     "iopub.status.idle": "2023-11-07T03:54:57.895775Z",
     "shell.execute_reply": "2023-11-07T03:54:57.895217Z",
     "shell.execute_reply.started": "2023-11-07T03:54:57.771882Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pickle\n",
    "\n",
    "# 指定要保存的文件名\n",
    "file_name = \"./feature/B_2_QRYTRNFLW.pkl\"\n",
    "\n",
    "# 使用pickle.dump()将特征矩阵保存为二进制文件\n",
    "with open(file_name, 'wb') as file:\n",
    "    pickle.dump(QRYTRNFLW_feature, file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.5 网银金融性流水表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:54:57.896895Z",
     "iopub.status.busy": "2023-11-07T03:54:57.896694Z",
     "iopub.status.idle": "2023-11-07T03:54:57.934836Z",
     "shell.execute_reply": "2023-11-07T03:54:57.934331Z",
     "shell.execute_reply.started": "2023-11-07T03:54:57.896870Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "EBANK_CSTLOG_train = pd.read_csv('../contest/train/EBANK_CSTLOG_QZ.csv') #网银金融性流水表\n",
    "EBANK_CSTLOG_B = pd.read_csv('../contest/B/EBANK_CSTLOG_QZ_B.csv') "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 日期修正"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:54:57.935830Z",
     "iopub.status.busy": "2023-11-07T03:54:57.935651Z",
     "iopub.status.idle": "2023-11-07T03:54:57.956893Z",
     "shell.execute_reply": "2023-11-07T03:54:57.956419Z",
     "shell.execute_reply.started": "2023-11-07T03:54:57.935808Z"
    }
   },
   "outputs": [],
   "source": [
    "# 训练集\n",
    "EBANK_CSTLOG_train['ADDFIELDDATE'] = pd.to_datetime(EBANK_CSTLOG_train['ADDFIELDDATE'], format='%Y%m%d')\n",
    "EBANK_CSTLOG_train['ADDFIELDDATE_year'] = EBANK_CSTLOG_train['ADDFIELDDATE'].dt.year\n",
    "EBANK_CSTLOG_train['ADDFIELDDATE_month'] = EBANK_CSTLOG_train['ADDFIELDDATE'].dt.month\n",
    "EBANK_CSTLOG_train['ADDFIELDDATE_day'] = EBANK_CSTLOG_train['ADDFIELDDATE'].dt.day\n",
    "\n",
    "EBANK_CSTLOG_train['ADDFIELDDATE_month'] =  EBANK_CSTLOG_train['ADDFIELDDATE_month'].replace(4,1)\n",
    "EBANK_CSTLOG_train['ADDFIELDDATE_month'] =  EBANK_CSTLOG_train['ADDFIELDDATE_month'].replace(5,2)\n",
    "EBANK_CSTLOG_train['ADDFIELDDATE_month'] =  EBANK_CSTLOG_train['ADDFIELDDATE_month'].replace(6,3)\n",
    "\n",
    "#B测试集\n",
    "EBANK_CSTLOG_B['ADDFIELDDATE'] = pd.to_datetime(EBANK_CSTLOG_B['ADDFIELDDATE'], format='%Y%m%d')\n",
    "EBANK_CSTLOG_B['ADDFIELDDATE_year'] = EBANK_CSTLOG_B['ADDFIELDDATE'].dt.year\n",
    "EBANK_CSTLOG_B['ADDFIELDDATE_month'] = EBANK_CSTLOG_B['ADDFIELDDATE'].dt.month\n",
    "EBANK_CSTLOG_B['ADDFIELDDATE_day'] = EBANK_CSTLOG_B['ADDFIELDDATE'].dt.day\n",
    "\n",
    "EBANK_CSTLOG_B['ADDFIELDDATE_month'] = EBANK_CSTLOG_B['ADDFIELDDATE_month'].replace(6,1)\n",
    "EBANK_CSTLOG_B['ADDFIELDDATE_month'] = EBANK_CSTLOG_B['ADDFIELDDATE_month'].replace(7,2)\n",
    "EBANK_CSTLOG_B['ADDFIELDDATE_month'] = EBANK_CSTLOG_B['ADDFIELDDATE_month'].replace(8,3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:54:57.957833Z",
     "iopub.status.busy": "2023-11-07T03:54:57.957662Z",
     "iopub.status.idle": "2023-11-07T03:54:58.590008Z",
     "shell.execute_reply": "2023-11-07T03:54:58.589463Z",
     "shell.execute_reply.started": "2023-11-07T03:54:57.957812Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "EBANK_CSTLOG_data=pd.DataFrame()\n",
    "EBANK_CSTLOG_data = pd.concat([EBANK_CSTLOG_train, EBANK_CSTLOG_B], axis = 0).reset_index(drop = True)\n",
    "#统一客户号字段名\n",
    "EBANK_CSTLOG_data=EBANK_CSTLOG_data.rename(columns={\"CSTNO\":\"CUST_NO\"})\n",
    "#拼表\n",
    "tr_EBANK_CSTLOG=target_data.merge(EBANK_CSTLOG_data,on=\"CUST_NO\",how=\"left\")\n",
    "tr_EBANK_CSTLOG[\"time_diff\"]=list(map(lambda app_time,time:quzheng(app_time,time),tr_EBANK_CSTLOG[\"DATA_DAT\"],tr_EBANK_CSTLOG[\"ADDFIELDDATE\"]))\n",
    "tr_EBANK_CSTLOG[\"time_diff\"]=tr_EBANK_CSTLOG[\"time_diff\"].astype('timedelta64[D]')\n",
    "tr_EBANK_CSTLOG=tr_EBANK_CSTLOG[tr_EBANK_CSTLOG[\"time_diff\"]>=0]\n",
    "col=['DATA_DAT','ADDFIELDDATE','ADDFIELDDATE_year']\n",
    "for i in col:\n",
    "    del tr_EBANK_CSTLOG[i]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.5.1 频数分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:54:58.591102Z",
     "iopub.status.busy": "2023-11-07T03:54:58.590903Z",
     "iopub.status.idle": "2023-11-07T03:54:59.642579Z",
     "shell.execute_reply": "2023-11-07T03:54:59.642100Z",
     "shell.execute_reply.started": "2023-11-07T03:54:58.591078Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(490, 49)"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 根据time_diff统计交易频数\n",
    "CSTLOG_1 = tr_EBANK_CSTLOG.copy(deep=True)\n",
    "CSTLOG_1_time_diff = make_gp(CSTLOG_1, ['CUST_NO', 'time_diff'], ['CUST_NO'], ['count'])\n",
    "CSTLOG_1_time_diff.columns = ['CUST_NO', 'time_diff', 'times']\n",
    "CSTLOG_1_time_diff_1 = make_gp(CSTLOG_1_time_diff, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOG_1_time_diff_1.columns = rename_df(CSTLOG_1_time_diff_1,'CUST_NO',\"CSTLOG\")\n",
    "# 根据TFT_DTE_month统计交易频数\n",
    "CSTLOG_1_month = make_gp(CSTLOG_1, ['CUST_NO', 'ADDFIELDDATE_month'], ['CUST_NO'], ['count'])\n",
    "CSTLOG_1_month.columns = ['CUST_NO', 'ADDFIELDDATE_month', 'montimes']\n",
    "CSTLOG_1_month_1 =  make_gp(CSTLOG_1_month, ['CUST_NO'], ['ADDFIELDDATE_month', 'montimes'], ['mean', 'sum', 'median', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOG_1_month_1.columns = rename_df(CSTLOG_1_month_1,'CUST_NO',\"CSTLOG_m\")\n",
    "# 根据TFT_DTE_day统计交易频数\n",
    "CSTLOG_1_day= make_gp(CSTLOG_1, ['CUST_NO', 'ADDFIELDDATE_day'], ['CUST_NO'], ['count'])\n",
    "CSTLOG_1_day.columns = ['CUST_NO', 'ADDFIELDDATE_day', 'daytimes']\n",
    "CSTLOG_1_day_1 = make_gp(CSTLOG_1_day, ['CUST_NO'], ['ADDFIELDDATE_day', 'daytimes'], ['mean', 'sum', 'median', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOG_1_day_1.columns = rename_df(CSTLOG_1_day_1,'CUST_NO',\"CSTLOG_d\")\n",
    "\n",
    "#汇总整理\n",
    "CSTLOG_1_huizong = pd.DataFrame()\n",
    "CSTLOG_1_huizong= CSTLOG_1_time_diff_1.merge(CSTLOG_1_month_1, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_1_huizong= CSTLOG_1_huizong.merge(CSTLOG_1_day_1, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_1_huizong.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.5.2 交易金额分组统计\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:54:59.643659Z",
     "iopub.status.busy": "2023-11-07T03:54:59.643466Z",
     "iopub.status.idle": "2023-11-07T03:55:00.540522Z",
     "shell.execute_reply": "2023-11-07T03:55:00.540046Z",
     "shell.execute_reply.started": "2023-11-07T03:54:59.643635Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(490, 36)"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#交易金额统计\n",
    "CSTLOG_am1 = tr_EBANK_CSTLOG.copy(deep=True)\n",
    "CSTLOG_am1_1 = make_gp(CSTLOG_am1,  ['CUST_NO'], ['TRNAMT'],['mean', 'sum', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOG_am1_1.columns = rename_df(CSTLOG_am1_1,'CUST_NO',\"CSTLOG_am\")\n",
    "#根据交易金额按月分组统计\n",
    "CSTLOG_am1_m = make_gp(CSTLOG_am1,  ['CUST_NO'], ['ADDFIELDDATE_month', 'TRNAMT'],['mean', 'sum', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOG_am1_m.columns = rename_df(CSTLOG_am1_m,'CUST_NO',\"CSTLOG_mam\")\n",
    "#根据交易金额按天分组统计\n",
    "CSTLOG_am1_d= make_gp(CSTLOG_am1,  ['CUST_NO'], ['ADDFIELDDATE_day', 'TRNAMT'],['mean', 'sum', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOG_am1_d.columns = rename_df(CSTLOG_am1_d,'CUST_NO',\"CSTLOG_dam\")\n",
    "#汇总整理\n",
    "CSTLOG_am1_huizong = pd.DataFrame()\n",
    "CSTLOG_am1_huizong= CSTLOG_am1_1.merge(CSTLOG_am1_m, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_am1_huizong= CSTLOG_am1_huizong.merge(CSTLOG_am1_d, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_am1_huizong.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.5.3 交易代码分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:55:00.541588Z",
     "iopub.status.busy": "2023-11-07T03:55:00.541402Z",
     "iopub.status.idle": "2023-11-07T03:55:00.590978Z",
     "shell.execute_reply": "2023-11-07T03:55:00.590524Z",
     "shell.execute_reply.started": "2023-11-07T03:55:00.541565Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(490, 11)"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#交易代码统计\n",
    "CSTLOG_cod1 = tr_EBANK_CSTLOG.copy(deep=True)\n",
    "CSTLOG_cod1_1 = make_gp(CSTLOG_cod1,  ['CUST_NO'], ['BSNCODE'],['count', 'nunique'])\n",
    "CSTLOG_cod1_1.columns = rename_df(CSTLOG_cod1_1,'CUST_NO',\"CSTLOG_cod\")\n",
    "\n",
    "#按月交易代码统计\n",
    "CSTLOG_cod1_m = make_gp(CSTLOG_cod1,  ['CUST_NO'], ['ADDFIELDDATE_month','BSNCODE'],['count', 'nunique'])\n",
    "CSTLOG_cod1_m.columns = rename_df(CSTLOG_cod1_m,'CUST_NO',\"CSTLOG_mcod\")\n",
    "#按天交易代码统计\n",
    "CSTLOG_cod1_d = make_gp(CSTLOG_cod1,  ['CUST_NO'], ['ADDFIELDDATE_day','BSNCODE'],['count', 'nunique'])\n",
    "CSTLOG_cod1_d.columns = rename_df(CSTLOG_cod1_d,'CUST_NO',\"CSTLOG_dcod\")\n",
    "#汇总整理\n",
    "CSTLOG_cod1_huizong = pd.DataFrame()\n",
    "CSTLOG_cod1_huizong= CSTLOG_cod1_1.merge(CSTLOG_cod1_m, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_cod1_huizong= CSTLOG_cod1_huizong.merge(CSTLOG_cod1_d, on = 'CUST_NO', how = 'left')\n",
    "\n",
    "CSTLOG_cod1_huizong.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.5.4 交易对手账号分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:55:00.591945Z",
     "iopub.status.busy": "2023-11-07T03:55:00.591768Z",
     "iopub.status.idle": "2023-11-07T03:55:00.640820Z",
     "shell.execute_reply": "2023-11-07T03:55:00.640383Z",
     "shell.execute_reply.started": "2023-11-07T03:55:00.591923Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(490, 11)"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#交易对手账号统计\n",
    "CSTLOG_acc1 = tr_EBANK_CSTLOG.copy(deep=True)\n",
    "CSTLOG_acc1_1 = make_gp(CSTLOG_acc1,  ['CUST_NO'], ['FRMACCTNO'],['count', 'nunique'])\n",
    "CSTLOG_acc1_1.columns = rename_df(CSTLOG_acc1_1,'CUST_NO',\"CSTLOG_acc\")\n",
    "\n",
    "#按月统计交易对手\n",
    "CSTLOG_acc1_m = make_gp(CSTLOG_acc1,  ['CUST_NO'], ['ADDFIELDDATE_month','FRMACCTNO'],['count', 'nunique'])\n",
    "CSTLOG_acc1_m.columns = rename_df(CSTLOG_acc1_m,'CUST_NO',\"CSTLOG_macc\")\n",
    "#按天统计交易对手\n",
    "CSTLOG_acc1_d = make_gp(CSTLOG_acc1,  ['CUST_NO'], ['ADDFIELDDATE_day','FRMACCTNO'],['count', 'nunique'])\n",
    "CSTLOG_acc1_d.columns = rename_df(CSTLOG_acc1_d,'CUST_NO',\"CSTLOG_dacc\")\n",
    "#汇总整理\n",
    "CSTLOG_acc1_huizong = pd.DataFrame()\n",
    "CSTLOG_acc1_huizong= CSTLOG_acc1_1.merge(CSTLOG_acc1_m, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_acc1_huizong= CSTLOG_acc1_huizong.merge(CSTLOG_acc1_d, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_acc1_huizong.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.5.5 汇总所有分组统计的结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:55:00.641722Z",
     "iopub.status.busy": "2023-11-07T03:55:00.641549Z",
     "iopub.status.idle": "2023-11-07T03:55:00.669993Z",
     "shell.execute_reply": "2023-11-07T03:55:00.669539Z",
     "shell.execute_reply.started": "2023-11-07T03:55:00.641700Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(490, 104)"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CSTLOG_feature=pd.DataFrame(EBANK_CSTLOG_data[\"CUST_NO\"].drop_duplicates())\n",
    "CSTLOG_feature=CSTLOG_feature.merge(CSTLOG_1_huizong, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_feature=CSTLOG_feature.merge(CSTLOG_am1_huizong, on = 'CUST_NO', how = 'left')  \n",
    "CSTLOG_feature=CSTLOG_feature.merge(CSTLOG_cod1_huizong, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_feature=CSTLOG_feature.merge(CSTLOG_acc1_huizong, on = 'CUST_NO', how = 'left') \n",
    "CSTLOG_feature.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.5.6 交易频数滑窗统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:55:00.670905Z",
     "iopub.status.busy": "2023-11-07T03:55:00.670738Z",
     "iopub.status.idle": "2023-11-07T03:55:01.997580Z",
     "shell.execute_reply": "2023-11-07T03:55:01.997026Z",
     "shell.execute_reply.started": "2023-11-07T03:55:00.670885Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(397, 65)"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#时间滑窗交易频数统计：近2周/近4周/近6周/近8周的频数、间隔及与全量之比例\n",
    "CSTLOG_2 = CSTLOG_1_time_diff.copy(deep=True) \n",
    "\n",
    "CSTLOG_2_2 = CSTLOG_2.loc[CSTLOG_2['time_diff']>42]\n",
    "CSTLOG_2_2 = make_gp(CSTLOG_2_2, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOG_2_2.columns = ['CUST_NO'] + [f +'_CSTLOG'+ '_last14' for f in CSTLOG_2_2.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "CSTLOG_2_4 = CSTLOG_2.loc[CSTLOG_2['time_diff']>28]\n",
    "CSTLOG_2_4 = make_gp(CSTLOG_2_4, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOG_2_4.columns = ['CUST_NO'] + [f +'_CSTLOG' + '_last28' for f in CSTLOG_2_4.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "CSTLOG_2_6 = CSTLOG_2.loc[CSTLOG_2['time_diff']>14]\n",
    "CSTLOG_2_6 = make_gp(CSTLOG_2_6, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOG_2_6.columns = ['CUST_NO'] + [f +'_CSTLOG'+ '_last42' for f in CSTLOG_2_6.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "CSTLOG_2_8 = CSTLOG_2.loc[CSTLOG_2['time_diff']>0]\n",
    "CSTLOG_2_8 = make_gp(CSTLOG_2_8, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOG_2_8.columns = ['CUST_NO'] + [f +'_CSTLOG'+ '_last56' for f in CSTLOG_2_8.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "#汇总整理\n",
    "CSTLOG_2_huizong=pd.DataFrame()\n",
    "CSTLOG_2_huizong=CSTLOG_2_2.merge(CSTLOG_2_4, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_2_huizong=CSTLOG_2_huizong.merge(CSTLOG_2_6, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_2_huizong=CSTLOG_2_huizong.merge(CSTLOG_2_8, on = 'CUST_NO', how = 'left')\n",
    "for d in [14, 28,42,56]:\n",
    "        CSTLOG_2_huizong[f'times_CSTLOG_rate_last{d}'] = CSTLOG_2_huizong[f'times_sum_CSTLOG_last{d}'] / CSTLOG_1_huizong[f'CSTLOG_times_sum']\n",
    "        CSTLOG_2_huizong[f'time_CSTLOG_diff_rate_last{d}'] = CSTLOG_2_huizong[f'time_diff_sum_CSTLOG_last{d}'] / CSTLOG_1_huizong[f'CSTLOG_time_diff_sum']\n",
    "CSTLOG_2_huizong.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.5.7 交易金额滑窗统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:55:01.998628Z",
     "iopub.status.busy": "2023-11-07T03:55:01.998438Z",
     "iopub.status.idle": "2023-11-07T03:55:02.065655Z",
     "shell.execute_reply": "2023-11-07T03:55:02.065084Z",
     "shell.execute_reply.started": "2023-11-07T03:55:01.998605Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#时间滑窗交易金额统计：：近2周/近4周/近6周/近8周的金额与与交易总金额之比\n",
    "CSTLOG_3 = tr_EBANK_CSTLOG.copy(deep=True)\n",
    "CSTLOG_3.sort_values(by = ['CUST_NO', 'time_diff']).reset_index(drop = True)\n",
    "\n",
    "CSTLOG_3_2 = make_gp(CSTLOG_3[(CSTLOG_3['time_diff']>42)], ['CUST_NO',], ['TRNAMT',], ['mean', 'sum', 'max', 'min'])\n",
    "CSTLOG_3_2.columns = ['CUST_NO'] + [f +'_CSTLOGamt'+ '_last14' for f in CSTLOG_3_2.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "CSTLOG_3_4 = make_gp(CSTLOG_3[(CSTLOG_3['time_diff']>28)], ['CUST_NO',], ['TRNAMT',], ['mean', 'sum', 'max', 'min'])\n",
    "CSTLOG_3_4.columns = ['CUST_NO'] + [f +'_CSTLOGamt'+ '_last28' for f in CSTLOG_3_4.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "CSTLOG_3_6 = make_gp(CSTLOG_3[(CSTLOG_3['time_diff']>14)], ['CUST_NO',], ['TRNAMT',], ['mean', 'sum', 'max', 'min'])\n",
    "CSTLOG_3_6.columns = ['CUST_NO'] + [f +'_CSTLOGamt' + '_last42' for f in CSTLOG_3_6.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "CSTLOG_3_8 = make_gp(CSTLOG_3[(CSTLOG_3['time_diff']>0)], ['CUST_NO',], ['TRNAMT',], ['mean', 'sum', 'max', 'min'])\n",
    "CSTLOG_3_8.columns = ['CUST_NO'] + [f +'_CSTLOGamt'+ '_last56' for f in CSTLOG_3_8.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "#汇总整理\n",
    "CSTLOG_3_huizong=pd.DataFrame()\n",
    "CSTLOG_3_huizong=CSTLOG_3_2.merge(CSTLOG_3_4, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_3_huizong=CSTLOG_3_huizong.merge(CSTLOG_3_6, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_3_huizong=CSTLOG_3_huizong.merge(CSTLOG_3_8, on = 'CUST_NO', how = 'left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 滑窗后的交易金额占比：（总—分）/总"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:55:02.066634Z",
     "iopub.status.busy": "2023-11-07T03:55:02.066453Z",
     "iopub.status.idle": "2023-11-07T03:55:02.103405Z",
     "shell.execute_reply": "2023-11-07T03:55:02.102916Z",
     "shell.execute_reply.started": "2023-11-07T03:55:02.066612Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "for col2 in ['TRNAMT', ]:\n",
    "    CSTLOG_3_huizong[f'{col2}_dr_sum_last28'] = (CSTLOG_am1_1[f'CSTLOG_am_{col2}_sum']- CSTLOG_3_huizong[f'{col2}_sum_CSTLOGamt_last28'])/CSTLOG_3_huizong[f'{col2}_sum_CSTLOGamt_last28']\n",
    "    CSTLOG_3_huizong[f'{col2}_dr_mean_last28'] = (CSTLOG_am1_1[f'CSTLOG_am_{col2}_mean']- CSTLOG_3_huizong[f'{col2}_mean_CSTLOGamt_last28'])/CSTLOG_3_huizong[f'{col2}_mean_CSTLOGamt_last28']\n",
    "    CSTLOG_3_huizong[f'{col2}_dr_max_last28']= (CSTLOG_am1_1[f'CSTLOG_am_{col2}_max']- CSTLOG_3_huizong[f'{col2}_max_CSTLOGamt_last28'])/CSTLOG_3_huizong[f'{col2}_max_CSTLOGamt_last28']                                                                                                        \n",
    "    CSTLOG_3_huizong[f'{col2}_dr_min_last28']= (CSTLOG_am1_1[f'CSTLOG_am_{col2}_min']- CSTLOG_3_huizong[f'{col2}_min_CSTLOGamt_last28'])/ CSTLOG_3_huizong[f'{col2}_min_CSTLOGamt_last28']                                                                                                          \n",
    "    \n",
    "    CSTLOG_3_huizong[f'{col2}_dr_sum_last28'] = (CSTLOG_am1_1[f'CSTLOG_am_{col2}_sum']- CSTLOG_3_huizong[f'{col2}_sum_CSTLOGamt_last28'])/CSTLOG_3_huizong[f'{col2}_sum_CSTLOGamt_last28']\n",
    "    CSTLOG_3_huizong[f'{col2}_dr_mean_last28'] = (CSTLOG_am1_1[f'CSTLOG_am_{col2}_mean']- CSTLOG_3_huizong[f'{col2}_mean_CSTLOGamt_last28'])/CSTLOG_3_huizong[f'{col2}_mean_CSTLOGamt_last28']\n",
    "    CSTLOG_3_huizong[f'{col2}_dr_max_last28']= (CSTLOG_am1_1[f'CSTLOG_am_{col2}_max']- CSTLOG_3_huizong[f'{col2}_max_CSTLOGamt_last28'])/CSTLOG_3_huizong[f'{col2}_max_CSTLOGamt_last28']                                                                                                     \n",
    "    CSTLOG_3_huizong[f'{col2}_dr_min_last28']= (CSTLOG_am1_1[f'CSTLOG_am_{col2}_min']- CSTLOG_3_huizong[f'{col2}_min_CSTLOGamt_last28'])/CSTLOG_3_huizong[f'{col2}_min_CSTLOGamt_last28']\n",
    "    \n",
    "    CSTLOG_3_huizong[f'{col2}_dr_sum_last42'] = (CSTLOG_am1_1[f'CSTLOG_am_{col2}_sum']- CSTLOG_3_huizong[f'{col2}_sum_CSTLOGamt_last42'])/CSTLOG_3_huizong[f'{col2}_sum_CSTLOGamt_last42']\n",
    "    CSTLOG_3_huizong[f'{col2}_dr_mean_last42'] = (CSTLOG_am1_1[f'CSTLOG_am_{col2}_mean']- CSTLOG_3_huizong[f'{col2}_mean_CSTLOGamt_last42'])/CSTLOG_3_huizong[f'{col2}_mean_CSTLOGamt_last42']\n",
    "    CSTLOG_3_huizong[f'{col2}_dr_max_last42']= (CSTLOG_am1_1[f'CSTLOG_am_{col2}_max']- CSTLOG_3_huizong[f'{col2}_max_CSTLOGamt_last42'])/CSTLOG_3_huizong[f'{col2}_max_CSTLOGamt_last42']                                                                                                       \n",
    "    CSTLOG_3_huizong[f'{col2}_dr_min_last42']= (CSTLOG_am1_1[f'CSTLOG_am_{col2}_min']- CSTLOG_3_huizong[f'{col2}_min_CSTLOGamt_last42'])/CSTLOG_3_huizong[f'{col2}_min_CSTLOGamt_last42']\n",
    "    \n",
    "    CSTLOG_3_huizong[f'{col2}_dr_sum_last56'] = (CSTLOG_am1_1[f'CSTLOG_am_{col2}_sum']- CSTLOG_3_huizong[f'{col2}_sum_CSTLOGamt_last56'])/CSTLOG_3_huizong[f'{col2}_sum_CSTLOGamt_last56']\n",
    "    CSTLOG_3_huizong[f'{col2}_dr_mean_last56'] = (CSTLOG_am1_1[f'CSTLOG_am_{col2}_mean']- CSTLOG_3_huizong[f'{col2}_mean_CSTLOGamt_last56'])/CSTLOG_3_huizong[f'{col2}_mean_CSTLOGamt_last56']\n",
    "    CSTLOG_3_huizong[f'{col2}_dr_max_last56']= (CSTLOG_am1_1[f'CSTLOG_am_{col2}_max']- CSTLOG_3_huizong[f'{col2}_max_CSTLOGamt_last56'])/CSTLOG_3_huizong[f'{col2}_max_CSTLOGamt_last56']                                                                                         \n",
    "    CSTLOG_3_huizong[f'{col2}_dr_min_last56']= (CSTLOG_am1_1[f'CSTLOG_am_{col2}_min']- CSTLOG_3_huizong[f'{col2}_min_CSTLOGamt_last56'])/CSTLOG_3_huizong[f'{col2}_min_CSTLOGamt_last56']\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.5.8 汇总所有的滑窗结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:55:02.104358Z",
     "iopub.status.busy": "2023-11-07T03:55:02.104175Z",
     "iopub.status.idle": "2023-11-07T03:55:02.127529Z",
     "shell.execute_reply": "2023-11-07T03:55:02.127080Z",
     "shell.execute_reply.started": "2023-11-07T03:55:02.104335Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(490, 196)"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CSTLOG_feature=CSTLOG_feature.merge(CSTLOG_2_huizong, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_feature=CSTLOG_feature.merge(CSTLOG_3_huizong, on = 'CUST_NO', how = 'left') \n",
    "CSTLOG_feature.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.5.9 保存CSTLOG_feature特征结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:55:02.128452Z",
     "iopub.status.busy": "2023-11-07T03:55:02.128244Z",
     "iopub.status.idle": "2023-11-07T03:55:02.136413Z",
     "shell.execute_reply": "2023-11-07T03:55:02.135941Z",
     "shell.execute_reply.started": "2023-11-07T03:55:02.128420Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pickle\n",
    "\n",
    "# 指定要保存的文件名\n",
    "file_name = \"./feature/B_3_CSTLOG.pkl\"\n",
    "\n",
    "# 使用pickle.dump()将特征矩阵保存为二进制文件\n",
    "with open(file_name, 'wb') as file:\n",
    "    pickle.dump(CSTLOG_feature, file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.6 网银非金融性流水表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:55:02.137305Z",
     "iopub.status.busy": "2023-11-07T03:55:02.137124Z",
     "iopub.status.idle": "2023-11-07T03:55:05.272488Z",
     "shell.execute_reply": "2023-11-07T03:55:05.271893Z",
     "shell.execute_reply.started": "2023-11-07T03:55:02.137284Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "EBANK_CSTLOGQUERY_train=pd.read_csv('../contest/train/EBANK_CSTLOGQUERY_QZ.csv')  #网银非金融流水表\n",
    "EBANK_CSTLOGQUERY_B  =pd.read_csv('../contest/B/EBANK_CSTLOGQUERY_QZ_B.csv') "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 日期修正"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:55:05.273625Z",
     "iopub.status.busy": "2023-11-07T03:55:05.273430Z",
     "iopub.status.idle": "2023-11-07T03:55:19.331684Z",
     "shell.execute_reply": "2023-11-07T03:55:19.331107Z",
     "shell.execute_reply.started": "2023-11-07T03:55:05.273602Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# 训练集\n",
    "EBANK_CSTLOGQUERY_train['CLQ_DTE_TIME'] = pd.to_datetime(EBANK_CSTLOGQUERY_train['CLQ_DTE_TIME'], format='%Y%m%d%H%M%S')\n",
    "EBANK_CSTLOGQUERY_train['CLQ_DTE_TIME_year'] = EBANK_CSTLOGQUERY_train['CLQ_DTE_TIME'].dt.year\n",
    "EBANK_CSTLOGQUERY_train['CLQ_DTE_TIME_month'] = EBANK_CSTLOGQUERY_train['CLQ_DTE_TIME'].dt.month\n",
    "EBANK_CSTLOGQUERY_train['CLQ_DTE_day'] = EBANK_CSTLOGQUERY_train['CLQ_DTE_TIME'].dt.day\n",
    "\n",
    "EBANK_CSTLOGQUERY_train['CLQ_DTE_TIME_month'] =  EBANK_CSTLOGQUERY_train['CLQ_DTE_TIME_month'].replace(4,1)\n",
    "EBANK_CSTLOGQUERY_train['CLQ_DTE_TIME_month'] =  EBANK_CSTLOGQUERY_train['CLQ_DTE_TIME_month'].replace(5,2)\n",
    "EBANK_CSTLOGQUERY_train['CLQ_DTE_TIME_month'] =  EBANK_CSTLOGQUERY_train['CLQ_DTE_TIME_month'].replace(6,3)\n",
    "\n",
    "#B测试集\n",
    "EBANK_CSTLOGQUERY_B['CLQ_DTE_TIME'] = pd.to_datetime(EBANK_CSTLOGQUERY_B['CLQ_DTE_TIME'], format='%Y%m%d%H%M%S')\n",
    "EBANK_CSTLOGQUERY_B['CLQ_DTE_TIME_year'] = EBANK_CSTLOGQUERY_B['CLQ_DTE_TIME'].dt.year\n",
    "EBANK_CSTLOGQUERY_B['CLQ_DTE_TIME_month'] = EBANK_CSTLOGQUERY_B['CLQ_DTE_TIME'].dt.month\n",
    "EBANK_CSTLOGQUERY_B['CLQ_DTE_day'] = EBANK_CSTLOGQUERY_B['CLQ_DTE_TIME'].dt.day\n",
    "\n",
    "EBANK_CSTLOGQUERY_B['CLQ_DTE_TIME_month'] = EBANK_CSTLOGQUERY_B['CLQ_DTE_TIME_month'].replace(6,1)\n",
    "EBANK_CSTLOGQUERY_B['CLQ_DTE_TIME_month'] = EBANK_CSTLOGQUERY_B['CLQ_DTE_TIME_month'].replace(7,2)\n",
    "EBANK_CSTLOGQUERY_B['CLQ_DTE_TIME_month'] = EBANK_CSTLOGQUERY_B['CLQ_DTE_TIME_month'].replace(8,3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:55:19.332786Z",
     "iopub.status.busy": "2023-11-07T03:55:19.332592Z",
     "iopub.status.idle": "2023-11-07T03:55:20.218108Z",
     "shell.execute_reply": "2023-11-07T03:55:20.217520Z",
     "shell.execute_reply.started": "2023-11-07T03:55:19.332763Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "EBANK_CSTLOGQUERY_data=pd.DataFrame()\n",
    "EBANK_CSTLOGQUERY_data = pd.concat([EBANK_CSTLOGQUERY_train, EBANK_CSTLOGQUERY_B], axis = 0).reset_index(drop = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:55:20.219195Z",
     "iopub.status.busy": "2023-11-07T03:55:20.218996Z",
     "iopub.status.idle": "2023-11-07T03:55:20.831201Z",
     "shell.execute_reply": "2023-11-07T03:55:20.830513Z",
     "shell.execute_reply.started": "2023-11-07T03:55:20.219171Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#统一客户号字段名\n",
    "EBANK_CSTLOGQUERY_data=EBANK_CSTLOGQUERY_data.rename(columns={\"CLQ_CSTNO\":\"CUST_NO\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:55:20.832339Z",
     "iopub.status.busy": "2023-11-07T03:55:20.832137Z",
     "iopub.status.idle": "2023-11-07T03:55:22.677507Z",
     "shell.execute_reply": "2023-11-07T03:55:22.676906Z",
     "shell.execute_reply.started": "2023-11-07T03:55:20.832315Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#拼表\n",
    "tr_EBANK_CSTLOGQUERY=target_data.merge(EBANK_CSTLOGQUERY_data,on=\"CUST_NO\",how=\"left\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:55:22.678644Z",
     "iopub.status.busy": "2023-11-07T03:55:22.678448Z",
     "iopub.status.idle": "2023-11-07T03:56:51.982332Z",
     "shell.execute_reply": "2023-11-07T03:56:51.981719Z",
     "shell.execute_reply.started": "2023-11-07T03:55:22.678620Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "tr_EBANK_CSTLOGQUERY[\"time_diff\"]=list(map(lambda app_time,time:quzheng(app_time,time),tr_EBANK_CSTLOGQUERY[\"DATA_DAT\"],tr_EBANK_CSTLOGQUERY[\"CLQ_DTE_TIME\"]))\n",
    "tr_EBANK_CSTLOGQUERY[\"time_diff\"]=tr_EBANK_CSTLOGQUERY[\"time_diff\"].astype('timedelta64[D]')\n",
    "tr_EBANK_CSTLOGQUERY=tr_EBANK_CSTLOGQUERY[tr_EBANK_CSTLOGQUERY[\"time_diff\"]>=0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:56:51.983469Z",
     "iopub.status.busy": "2023-11-07T03:56:51.983265Z",
     "iopub.status.idle": "2023-11-07T03:56:52.136664Z",
     "shell.execute_reply": "2023-11-07T03:56:52.136143Z",
     "shell.execute_reply.started": "2023-11-07T03:56:51.983445Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "col=['DATA_DAT','CLQ_DTE_TIME','CLQ_DTE_TIME_year']\n",
    "for i in col:\n",
    "    del tr_EBANK_CSTLOGQUERY[i]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.6.1 频数分组统计"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 根据time_diff统计交易频数/分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:56:52.137736Z",
     "iopub.status.busy": "2023-11-07T03:56:52.137546Z",
     "iopub.status.idle": "2023-11-07T03:56:57.795481Z",
     "shell.execute_reply": "2023-11-07T03:56:57.794980Z",
     "shell.execute_reply.started": "2023-11-07T03:56:52.137713Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1082, 49)"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 根据time_diff统计交易频数\n",
    "CSTLOGQUERY_1 = tr_EBANK_CSTLOGQUERY.copy(deep=True)\n",
    "CSTLOGQUERY_1_time_diff = make_gp(CSTLOGQUERY_1, ['CUST_NO', 'time_diff'], ['CUST_NO'], ['count'])\n",
    "CSTLOGQUERY_1_time_diff.columns = ['CUST_NO', 'time_diff', 'times']\n",
    "CSTLOGQUERY_1_time_diff_1 = make_gp(CSTLOGQUERY_1_time_diff, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOGQUERY_1_time_diff_1.columns = rename_df(CSTLOGQUERY_1_time_diff_1,'CUST_NO',\"CSTLOGQUERY\")\n",
    "\n",
    "# 根据TFT_DTE_month统计交易频数\n",
    "CSTLOGQUERY_1_month = make_gp(CSTLOGQUERY_1, ['CUST_NO', 'CLQ_DTE_TIME_month'], ['CUST_NO'], ['count'])\n",
    "CSTLOGQUERY_1_month.columns = ['CUST_NO', 'CLQ_DTE_TIME_month', 'montimes']\n",
    "CSTLOGQUERY_1_month_1 =  make_gp(CSTLOGQUERY_1_month, ['CUST_NO'], ['CLQ_DTE_TIME_month', 'montimes'], ['mean', 'sum', 'median', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOGQUERY_1_month_1.columns = rename_df(CSTLOGQUERY_1_month_1,'CUST_NO',\"CSTLOGQUERY_m\")\n",
    "\n",
    "# 根据TFT_DTE_day统计交易频数\n",
    "CSTLOGQUERY_1_day= make_gp(CSTLOGQUERY_1, ['CUST_NO', 'CLQ_DTE_day'], ['CUST_NO'], ['count'])\n",
    "CSTLOGQUERY_1_day.columns = ['CUST_NO', 'CLQ_DTE_day', 'daytimes']\n",
    "CSTLOGQUERY_1_day_1 = make_gp(CSTLOGQUERY_1_day, ['CUST_NO'], ['CLQ_DTE_day', 'daytimes'], ['mean', 'sum', 'median', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOGQUERY_1_day_1.columns = rename_df(CSTLOGQUERY_1_day_1,'CUST_NO',\"CSTLOGQUERY_d\")\n",
    "\n",
    "#汇总整理\n",
    "CSTLOGQUERY_1_huizong = pd.DataFrame()\n",
    "CSTLOGQUERY_1_huizong= CSTLOGQUERY_1_time_diff_1.merge(CSTLOGQUERY_1_month_1, on = 'CUST_NO', how = 'left')\n",
    "CSTLOGQUERY_1_huizong= CSTLOGQUERY_1_huizong.merge(CSTLOGQUERY_1_day_1, on = 'CUST_NO', how = 'left')    \n",
    "CSTLOGQUERY_1_huizong.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.6.2 交易代码分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:56:57.796589Z",
     "iopub.status.busy": "2023-11-07T03:56:57.796392Z",
     "iopub.status.idle": "2023-11-07T03:57:05.660041Z",
     "shell.execute_reply": "2023-11-07T03:57:05.659547Z",
     "shell.execute_reply.started": "2023-11-07T03:56:57.796565Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1082, 11)"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#交易代码统计\n",
    "CSTLOGQUERY_cod1 = tr_EBANK_CSTLOGQUERY.copy(deep=True)\n",
    "CSTLOGQUERY_cod1_1 = make_gp(CSTLOGQUERY_cod1,  ['CUST_NO'], ['CLQ_BSNCOD'],['count', 'nunique'])\n",
    "CSTLOGQUERY_cod1_1.columns = rename_df(CSTLOGQUERY_cod1_1,'CUST_NO',\"CSTLOGQUERY_cod\")\n",
    "\n",
    "#按月交易代码统计\n",
    "CSTLOGQUERY_cod1_m = make_gp(CSTLOGQUERY_cod1,  ['CUST_NO'], ['CLQ_DTE_TIME_month','CLQ_BSNCOD'],['count', 'nunique'])\n",
    "CSTLOGQUERY_cod1_m.columns = rename_df(CSTLOGQUERY_cod1_m,'CUST_NO',\"CSTLOGQUERY_mcod\")\n",
    "\n",
    "#按天交易代码统计\n",
    "CSTLOGQUERY_cod1_d = make_gp(CSTLOGQUERY_cod1,  ['CUST_NO'], ['CLQ_DTE_day','CLQ_BSNCOD'],['count', 'nunique'])\n",
    "CSTLOGQUERY_cod1_d.columns = rename_df(CSTLOGQUERY_cod1_d,'CUST_NO',\"CSTLOGQUERY_dcod\")\n",
    "\n",
    "#汇总整理\n",
    "CSTLOGQUERY_cod1_huizong = pd.DataFrame()\n",
    "CSTLOGQUERY_cod1_huizong  =CSTLOGQUERY_cod1_1.merge(CSTLOGQUERY_cod1_m, on = 'CUST_NO', how = 'left')\n",
    "CSTLOGQUERY_cod1_huizong  =CSTLOGQUERY_cod1_huizong.merge(CSTLOGQUERY_cod1_d, on = 'CUST_NO', how = 'left')\n",
    "CSTLOGQUERY_cod1_huizong.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.6.3 汇总所有分组统计的结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:57:05.661111Z",
     "iopub.status.busy": "2023-11-07T03:57:05.660920Z",
     "iopub.status.idle": "2023-11-07T03:57:05.835999Z",
     "shell.execute_reply": "2023-11-07T03:57:05.835543Z",
     "shell.execute_reply.started": "2023-11-07T03:57:05.661087Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1082, 59)"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CSTLOGQUERY_feature=pd.DataFrame(EBANK_CSTLOGQUERY_data[\"CUST_NO\"].drop_duplicates())\n",
    "CSTLOGQUERY_feature = CSTLOGQUERY_feature.merge(CSTLOGQUERY_1_huizong, on = 'CUST_NO', how = 'left')\n",
    "CSTLOGQUERY_feature = CSTLOGQUERY_feature.merge(CSTLOGQUERY_cod1_huizong, on = 'CUST_NO', how = 'left')\n",
    "CSTLOGQUERY_feature.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.6.4 交易频数滑窗统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:57:05.837019Z",
     "iopub.status.busy": "2023-11-07T03:57:05.836839Z",
     "iopub.status.idle": "2023-11-07T03:57:08.638213Z",
     "shell.execute_reply": "2023-11-07T03:57:08.637568Z",
     "shell.execute_reply.started": "2023-11-07T03:57:05.836997Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#时间滑窗交易频数统计：近2周/近4周/近6周/近8周的频数、间隔及与全量之比例\n",
    "CSTLOGQUERY_2 = CSTLOGQUERY_1_time_diff.copy(deep=True) \n",
    "\n",
    "CSTLOGQUERY_2_2 = CSTLOGQUERY_2.loc[CSTLOGQUERY_2['time_diff']>42]\n",
    "CSTLOGQUERY_2_2 = make_gp(CSTLOGQUERY_2_2, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOGQUERY_2_2.columns = ['CUST_NO'] + [f +'_CSTLOGQUERY'+ '_last14' for f in CSTLOGQUERY_2_2.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "CSTLOGQUERY_2_4 = CSTLOGQUERY_2.loc[CSTLOGQUERY_2['time_diff']>28]\n",
    "CSTLOGQUERY_2_4 = make_gp(CSTLOGQUERY_2_4, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOGQUERY_2_4.columns = ['CUST_NO'] + [f +'_CSTLOGQUERY'+ '_last28' for f in CSTLOGQUERY_2_4.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "CSTLOGQUERY_2_6 = CSTLOGQUERY_2.loc[CSTLOGQUERY_2['time_diff']>14]\n",
    "CSTLOGQUERY_2_6 = make_gp(CSTLOGQUERY_2_6, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOGQUERY_2_6.columns = ['CUST_NO'] + [f +'_CSTLOGQUERY'+ '_last42' for f in CSTLOGQUERY_2_6.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "CSTLOGQUERY_2_8 = CSTLOGQUERY_2.loc[CSTLOGQUERY_2['time_diff']>0]\n",
    "CSTLOGQUERY_2_8 = make_gp(CSTLOGQUERY_2_8, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "CSTLOGQUERY_2_8.columns = ['CUST_NO'] + [f +'_CSTLOGQUERY'+ '_last56' for f in CSTLOGQUERY_2_8.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "#汇总整理\n",
    "CSTLOGQUERY_2_huizong=pd.DataFrame()\n",
    "CSTLOGQUERY_2_huizong=CSTLOGQUERY_2_2.merge(CSTLOGQUERY_2_4, on = 'CUST_NO', how = 'left')\n",
    "CSTLOGQUERY_2_huizong=CSTLOGQUERY_2_huizong.merge(CSTLOGQUERY_2_6, on = 'CUST_NO', how = 'left')\n",
    "CSTLOGQUERY_2_huizong=CSTLOGQUERY_2_huizong.merge(CSTLOGQUERY_2_8, on = 'CUST_NO', how = 'left')\n",
    "\n",
    "###作比\n",
    "for d in [14, 28,42,56]:\n",
    "        CSTLOGQUERY_2_huizong[f'times_CSTLOGQUERY_rate_last{d}'] = CSTLOGQUERY_2_huizong[f'times_sum_CSTLOGQUERY_last{d}'] / CSTLOGQUERY_1_huizong[f'CSTLOGQUERY_times_sum']\n",
    "        CSTLOGQUERY_2_huizong[f'time_CSTLOGQUERY_diff_rate_last{d}'] = CSTLOGQUERY_2_huizong[f'time_diff_sum_CSTLOGQUERY_last{d}'] / CSTLOGQUERY_1_huizong[f'CSTLOGQUERY_time_diff_sum']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.6.5 汇总所有结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:57:08.639240Z",
     "iopub.status.busy": "2023-11-07T03:57:08.639050Z",
     "iopub.status.idle": "2023-11-07T03:57:08.655304Z",
     "shell.execute_reply": "2023-11-07T03:57:08.654834Z",
     "shell.execute_reply.started": "2023-11-07T03:57:08.639216Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1082, 123)"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CSTLOGQUERY_feature=CSTLOGQUERY_feature.merge(CSTLOGQUERY_2_huizong, on = 'CUST_NO', how = 'left')\n",
    "CSTLOGQUERY_feature.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.6.6 保存CSTLOGQUERY_feature 特征"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:57:08.656263Z",
     "iopub.status.busy": "2023-11-07T03:57:08.656085Z",
     "iopub.status.idle": "2023-11-07T03:57:08.665541Z",
     "shell.execute_reply": "2023-11-07T03:57:08.665069Z",
     "shell.execute_reply.started": "2023-11-07T03:57:08.656241Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pickle\n",
    "\n",
    "# 指定要保存的文件名\n",
    "file_name = \"./feature/B_4_CSTLOGQUERY.pkl\"\n",
    "\n",
    "# 使用pickle.dump()将特征矩阵保存为二进制文件\n",
    "with open(file_name, 'wb') as file:\n",
    "    pickle.dump(CSTLOGQUERY_feature, file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.7 借记卡交易流水表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:57:08.666437Z",
     "iopub.status.busy": "2023-11-07T03:57:08.666262Z",
     "iopub.status.idle": "2023-11-07T03:57:22.925427Z",
     "shell.execute_reply": "2023-11-07T03:57:22.924837Z",
     "shell.execute_reply.started": "2023-11-07T03:57:08.666415Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "APS_train = pd.read_csv('../contest/train/APS_QZ.csv') #借记卡流水表\n",
    "APS_B = pd.read_csv('../contest/B/APS_QZ_B.csv') "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 日期修正"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:57:22.926547Z",
     "iopub.status.busy": "2023-11-07T03:57:22.926354Z",
     "iopub.status.idle": "2023-11-07T03:57:50.203794Z",
     "shell.execute_reply": "2023-11-07T03:57:50.203202Z",
     "shell.execute_reply.started": "2023-11-07T03:57:22.926523Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# 年月日维度转换\n",
    "#训练集\n",
    "APS_train['APSDTRDAT_TM'] = pd.to_datetime(APS_train['APSDTRDAT_TM'], format='%Y%m%d%H%M%S')\n",
    "APS_train['APSDTRDAT_TM_year'] = APS_train['APSDTRDAT_TM'].dt.year\n",
    "APS_train['APSDTRDAT_TM_month'] = APS_train['APSDTRDAT_TM'].dt.month\n",
    "APS_train['APSDTRDAT_TM_day'] = APS_train['APSDTRDAT_TM'].dt.day\n",
    "\n",
    "APS_train['APSDTRDAT_TM_month'] =  APS_train['APSDTRDAT_TM_month'].replace(4,1)\n",
    "APS_train['APSDTRDAT_TM_month'] =  APS_train['APSDTRDAT_TM_month'].replace(5,2)\n",
    "APS_train['APSDTRDAT_TM_month'] =  APS_train['APSDTRDAT_TM_month'].replace(6,3)\n",
    "\n",
    "#B测试集\n",
    "APS_B['APSDTRDAT_TM'] = pd.to_datetime(APS_B['APSDTRDAT_TM'], format='%Y%m%d%H%M%S')\n",
    "APS_B['APSDTRDAT_TM_year'] = APS_B['APSDTRDAT_TM'].dt.year\n",
    "APS_B['APSDTRDAT_TM_month'] = APS_B['APSDTRDAT_TM'].dt.month\n",
    "APS_B['APSDTRDAT_TM_day'] = APS_B['APSDTRDAT_TM'].dt.day\n",
    "\n",
    "APS_B['APSDTRDAT_TM_month'] = APS_B['APSDTRDAT_TM_month'].replace(6,1)\n",
    "APS_B['APSDTRDAT_TM_month'] = APS_B['APSDTRDAT_TM_month'].replace(7,2)\n",
    "APS_B['APSDTRDAT_TM_month'] = APS_B['APSDTRDAT_TM_month'].replace(8,3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:57:50.204941Z",
     "iopub.status.busy": "2023-11-07T03:57:50.204747Z",
     "iopub.status.idle": "2023-11-07T03:57:54.651549Z",
     "shell.execute_reply": "2023-11-07T03:57:54.650929Z",
     "shell.execute_reply.started": "2023-11-07T03:57:50.204916Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "APS_data=pd.DataFrame()\n",
    "APS_data = pd.concat([APS_train, APS_B], axis = 0).reset_index(drop = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:57:54.652696Z",
     "iopub.status.busy": "2023-11-07T03:57:54.652496Z",
     "iopub.status.idle": "2023-11-07T03:57:56.726901Z",
     "shell.execute_reply": "2023-11-07T03:57:56.726300Z",
     "shell.execute_reply.started": "2023-11-07T03:57:54.652672Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#统一卡号字段名\n",
    "APS_data=APS_data.rename(columns={\"APSDPRDNO\":\"CARD_NO\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:57:56.728031Z",
     "iopub.status.busy": "2023-11-07T03:57:56.727838Z",
     "iopub.status.idle": "2023-11-07T03:58:04.167114Z",
     "shell.execute_reply": "2023-11-07T03:58:04.166501Z",
     "shell.execute_reply.started": "2023-11-07T03:57:56.728007Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#拼表\n",
    "tr_APS=target_data.merge(APS_data,on=\"CARD_NO\",how=\"left\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T03:58:04.168276Z",
     "iopub.status.busy": "2023-11-07T03:58:04.168077Z",
     "iopub.status.idle": "2023-11-07T04:01:02.318614Z",
     "shell.execute_reply": "2023-11-07T04:01:02.317983Z",
     "shell.execute_reply.started": "2023-11-07T03:58:04.168251Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "tr_APS[\"time_diff\"]=list(map(lambda app_time,time:quzheng(app_time,time),tr_APS[\"DATA_DAT\"],tr_APS[\"APSDTRDAT_TM\"]))\n",
    "tr_APS[\"time_diff\"]=tr_APS[\"time_diff\"].astype('timedelta64[D]')\n",
    "tr_APS=tr_APS[tr_APS[\"time_diff\"]>=0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:01:02.319768Z",
     "iopub.status.busy": "2023-11-07T04:01:02.319570Z",
     "iopub.status.idle": "2023-11-07T04:01:02.851904Z",
     "shell.execute_reply": "2023-11-07T04:01:02.851296Z",
     "shell.execute_reply.started": "2023-11-07T04:01:02.319743Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "col=['DATA_DAT','APSDTRDAT_TM','APSDTRDAT_TM_year']\n",
    "for i in col:\n",
    "    del tr_APS[i]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.7.1 频数分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:01:02.853091Z",
     "iopub.status.busy": "2023-11-07T04:01:02.852883Z",
     "iopub.status.idle": "2023-11-07T04:02:36.219532Z",
     "shell.execute_reply": "2023-11-07T04:02:36.218940Z",
     "shell.execute_reply.started": "2023-11-07T04:01:02.853066Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# 根据time_diff统计交易频数\n",
    "APS_1 = tr_APS.copy(deep=True)\n",
    "APS_1_time_diff = make_gp(APS_1, ['CUST_NO', 'time_diff'], ['CUST_NO'], ['count'])\n",
    "APS_1_time_diff.columns = ['CUST_NO', 'time_diff', 'times']\n",
    "APS_1_time_diff_1 = make_gp(APS_1_time_diff, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "APS_1_time_diff_1.columns = rename_df(APS_1_time_diff_1,'CUST_NO',\"APS\")\n",
    "\n",
    "#根据TFT_DTE_month统计交易频数/分组统计\n",
    "APS_1_month = make_gp(APS_1, ['CUST_NO', 'APSDTRDAT_TM_month'], ['CUST_NO'], ['count'])\n",
    "APS_1_month.columns = ['CUST_NO', 'APSDTRDAT_TM_month', 'montimes']\n",
    "APS_1_month_1 =  make_gp(APS_1_month, ['CUST_NO'], ['APSDTRDAT_TM_month', 'montimes'], ['mean', 'sum', 'median', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "APS_1_month_1.columns = rename_df(APS_1_month_1,'CUST_NO',\"APS_m\")\n",
    "\n",
    "#根据TFT_DTE_day统计交易频数\n",
    "APS_1_day= make_gp(APS_1, ['CUST_NO', 'APSDTRDAT_TM_day'], ['CUST_NO'], ['count'])\n",
    "APS_1_day.columns = ['CUST_NO', 'APSDTRDAT_TM_day', 'daytimes']\n",
    "APS_1_day_1 = make_gp(APS_1_day, ['CUST_NO'], ['APSDTRDAT_TM_day', 'daytimes'], ['mean', 'sum', 'median', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "\n",
    "APS_1_day_1.columns = rename_df(APS_1_day_1,'CUST_NO',\"APS_d\")\n",
    "\n",
    "#汇总整理\n",
    "APS_1_huizong = pd.DataFrame()\n",
    "APS_1_huizong= APS_1_time_diff_1.merge(APS_1_month_1, on = 'CUST_NO', how = 'left')\n",
    "APS_1_huizong= APS_1_huizong.merge(APS_1_day_1, on = 'CUST_NO', how = 'left')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.7.2 转出金额分组统计"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 根据总转出交易金额统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:02:36.220730Z",
     "iopub.status.busy": "2023-11-07T04:02:36.220526Z",
     "iopub.status.idle": "2023-11-07T04:03:57.812526Z",
     "shell.execute_reply": "2023-11-07T04:03:57.811919Z",
     "shell.execute_reply.started": "2023-11-07T04:02:36.220705Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#交易金额统计\n",
    "APS_am1 = tr_APS[tr_APS['APSDTRAMT']<0].copy(deep=True)\n",
    "APS_am1_1 = make_gp(APS_am1,  ['CUST_NO'], ['APSDTRAMT'],['mean', 'sum', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "APS_am1_1.columns = rename_df(APS_am1_1,'CUST_NO',\"APS_outamt\")\n",
    "\n",
    "#根据交易金额按月分组统计\n",
    "APS_am1_m = make_gp(APS_am1,  ['CUST_NO'], ['APSDTRDAT_TM_month', 'APSDTRAMT'],['mean', 'sum', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "APS_am1_m.columns = rename_df(APS_am1_m,'CUST_NO',\"APS_moutamt\")\n",
    "\n",
    "#根据交易金额按天分组统计\n",
    "APS_am1_d= make_gp(APS_am1,  ['CUST_NO'], ['APSDTRDAT_TM_day', 'APSDTRAMT'],['mean', 'sum', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "APS_am1_d.columns = rename_df(APS_am1_d,'CUST_NO',\"APS_doutamt\")\n",
    "\n",
    "#汇总整理\n",
    "APS_am1_out_huizong = pd.DataFrame()\n",
    "APS_am1_out_huizong= APS_am1_1.merge(APS_am1_m, on = 'CUST_NO', how = 'left')\n",
    "APS_am1_out_huizong= APS_am1_out_huizong.merge(APS_am1_d, on = 'CUST_NO', how = 'left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.7.3 转入金额分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:03:57.813668Z",
     "iopub.status.busy": "2023-11-07T04:03:57.813474Z",
     "iopub.status.idle": "2023-11-07T04:05:14.499926Z",
     "shell.execute_reply": "2023-11-07T04:05:14.499334Z",
     "shell.execute_reply.started": "2023-11-07T04:03:57.813645Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#交易金额统计\n",
    "APS_am2 = tr_APS[tr_APS['APSDTRAMT']>0].copy(deep=True)\n",
    "APS_am2_1 = make_gp(APS_am2,  ['CUST_NO'], ['APSDTRAMT'],['mean', 'sum', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "APS_am2_1.columns = rename_df(APS_am2_1,'CUST_NO',\"APS_inamt\")\n",
    "\n",
    "#根据交易金额按月分组统计\n",
    "APS_am2_m = make_gp(APS_am2,  ['CUST_NO'], ['APSDTRDAT_TM_month', 'APSDTRAMT'],['mean', 'sum', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "APS_am2_m.columns = rename_df(APS_am2_m,'CUST_NO',\"APS_inmamt\")\n",
    "\n",
    "#根据交易金额按天分组统计\n",
    "APS_am2_d= make_gp(APS_am2,  ['CUST_NO'], ['APSDTRDAT_TM_day', 'APSDTRAMT'],['mean', 'sum', 'max','min', 'skew', 'std', pd.DataFrame.kurt])\n",
    "APS_am2_d.columns = rename_df(APS_am2_d,'CUST_NO',\"APS_indamt\")\n",
    "\n",
    "#汇总整理\n",
    "APS_am2_in_huizong = pd.DataFrame()\n",
    "APS_am2_in_huizong= APS_am2_1.merge(APS_am2_m, on = 'CUST_NO', how = 'left')\n",
    "APS_am2_in_huizong= APS_am2_in_huizong.merge(APS_am2_d, on = 'CUST_NO', how = 'left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.7.4 交易代码分组统计"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 根据总交易代码统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:05:14.501052Z",
     "iopub.status.busy": "2023-11-07T04:05:14.500850Z",
     "iopub.status.idle": "2023-11-07T04:05:31.571153Z",
     "shell.execute_reply": "2023-11-07T04:05:31.570560Z",
     "shell.execute_reply.started": "2023-11-07T04:05:14.501020Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#根据总交易代码统计\n",
    "APS_cod1 = tr_APS.copy(deep=True)\n",
    "APS_cod1_1 = make_gp(APS_cod1,  ['CUST_NO'], ['APSDTRCOD'],['count', 'nunique'])\n",
    "APS_cod1_1.columns = rename_df(APS_cod1_1,'CUST_NO',\"APS_cod\")\n",
    "\n",
    "#按月交易代码统计\n",
    "APS_cod1_m = make_gp(APS_cod1,  ['CUST_NO'], ['APSDTRDAT_TM_month','APSDTRCOD'],['count', 'nunique'])\n",
    "APS_cod1_m.columns = rename_df(APS_cod1_m,'CUST_NO',\"APS_mcod\")\n",
    "\n",
    "#按天交易代码统计\n",
    "APS_cod1_d = make_gp(APS_cod1,  ['CUST_NO'], ['APSDTRDAT_TM_day','APSDTRCOD'],['count', 'nunique'])\n",
    "APS_cod1_d.columns = rename_df(APS_cod1_d,'CUST_NO',\"APS_dcod\")\n",
    "\n",
    "#汇总整理\n",
    "APS_cod1_huizong = pd.DataFrame()\n",
    "APS_cod1_huizong= APS_cod1_1.merge(APS_cod1_m, on = 'CUST_NO', how = 'left')\n",
    "APS_cod1_huizong= APS_cod1_huizong.merge(APS_cod1_d, on = 'CUST_NO', how = 'left')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.7.5 交易渠道分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:05:31.572293Z",
     "iopub.status.busy": "2023-11-07T04:05:31.572099Z",
     "iopub.status.idle": "2023-11-07T04:05:48.034371Z",
     "shell.execute_reply": "2023-11-07T04:05:48.033776Z",
     "shell.execute_reply.started": "2023-11-07T04:05:31.572269Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#根据总交易渠道统计\n",
    "APS_chl1 = tr_APS.copy(deep=True)\n",
    "APS_chl1_1 = make_gp(APS_chl1,  ['CUST_NO'], ['APSDTRCHL'],['count', 'nunique'])\n",
    "APS_chl1_1.columns = rename_df(APS_chl1_1,'CUST_NO',\"APS_chl\")\n",
    "\n",
    "#按月交易代码统计\n",
    "APS_chl1_m = make_gp(APS_chl1,  ['CUST_NO'], ['APSDTRDAT_TM_month','APSDTRCHL'],['count', 'nunique'])\n",
    "APS_chl1_m.columns = rename_df(APS_chl1_m,'CUST_NO',\"APS_mchl\")\n",
    "\n",
    "#按天交易代码统计\n",
    "APS_chl1_d = make_gp(APS_chl1,  ['CUST_NO'], ['APSDTRDAT_TM_day','APSDTRCHL'],['count', 'nunique'])\n",
    "APS_chl1_d.columns = rename_df(APS_chl1_d,'CUST_NO',\"APS_dchl\")\n",
    "\n",
    "#汇总整理\n",
    "APS_chl1_huizong = pd.DataFrame()\n",
    "APS_chl1_huizong= APS_chl1_1.merge(APS_chl1_m, on = 'CUST_NO', how = 'left')\n",
    "APS_chl1_huizong= APS_chl1_huizong.merge(APS_chl1_d, on = 'CUST_NO', how = 'left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.7.6 交易对手账号分组统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:05:48.035508Z",
     "iopub.status.busy": "2023-11-07T04:05:48.035316Z",
     "iopub.status.idle": "2023-11-07T04:06:08.855667Z",
     "shell.execute_reply": "2023-11-07T04:06:08.855084Z",
     "shell.execute_reply.started": "2023-11-07T04:05:48.035484Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#交易对手账号统计\n",
    "APS_acc1 = tr_APS.copy(deep=True)\n",
    "APS_acc1_1 = make_gp(APS_acc1,  ['CUST_NO'], ['APSDCPTPRDNO'],['count', 'nunique'])\n",
    "APS_acc1_1.columns = rename_df(APS_acc1_1,'CUST_NO',\"APS_acc\")\n",
    "\n",
    "#按月交易代码统计\n",
    "APS_acc1_m = make_gp(APS_acc1,  ['CUST_NO'], ['APSDTRDAT_TM_month','APSDCPTPRDNO'],['count', 'nunique'])\n",
    "APS_acc1_m.columns = rename_df(APS_acc1_m,'CUST_NO',\"APS_macc\")\n",
    "\n",
    "#按天统计交易对手\n",
    "APS_acc1_d = make_gp(APS_acc1,  ['CUST_NO'], ['APSDTRDAT_TM_day','APSDCPTPRDNO'],['count', 'nunique'])\n",
    "APS_acc1_d.columns = rename_df(APS_acc1_d,'CUST_NO',\"APS_dacc\")\n",
    "\n",
    "#汇总整理\n",
    "APS_acc1_huizong = pd.DataFrame()\n",
    "APS_acc1_huizong= APS_acc1_1.merge(APS_acc1_m, on = 'CUST_NO', how = 'left')\n",
    "APS_acc1_huizong= APS_acc1_huizong.merge(APS_acc1_d, on = 'CUST_NO', how = 'left')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.7.7 汇总所有分组统计的结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:06:08.856768Z",
     "iopub.status.busy": "2023-11-07T04:06:08.856575Z",
     "iopub.status.idle": "2023-11-07T04:06:09.530755Z",
     "shell.execute_reply": "2023-11-07T04:06:09.530153Z",
     "shell.execute_reply.started": "2023-11-07T04:06:08.856744Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "APS_feature=pd.DataFrame(tr_APS[\"CUST_NO\"].drop_duplicates())\n",
    "APS_feature=APS_feature.merge(APS_1_huizong, on = 'CUST_NO', how = 'left')\n",
    "APS_feature=APS_feature.merge(APS_am1_out_huizong, on = 'CUST_NO', how = 'left')  \n",
    "APS_feature=APS_feature.merge(APS_am2_in_huizong, on = 'CUST_NO', how = 'left')  \n",
    "APS_feature=APS_feature.merge(APS_cod1_huizong, on = 'CUST_NO', how = 'left')\n",
    "APS_feature=APS_feature.merge(APS_chl1_huizong, on = 'CUST_NO', how = 'left')\n",
    "APS_feature=APS_feature.merge(APS_acc1_huizong, on = 'CUST_NO', how = 'left') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:06:09.531864Z",
     "iopub.status.busy": "2023-11-07T04:06:09.531670Z",
     "iopub.status.idle": "2023-11-07T04:06:09.760440Z",
     "shell.execute_reply": "2023-11-07T04:06:09.759879Z",
     "shell.execute_reply.started": "2023-11-07T04:06:09.531840Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(42016, 147)"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 空值率高\n",
    "null_percentage = APS_feature.isnull().mean()\n",
    "threshold = 0.95\n",
    "null_high_col_aps1 = []\n",
    "null_high_col_aps1 = null_percentage[null_percentage>threshold].index.tolist()\n",
    "\n",
    "feature_name_APS = [i for i in APS_feature.columns if i not in null_high_col_aps1]\n",
    "APS_feature = APS_feature[feature_name_APS].reset_index(drop=True)\n",
    "\n",
    "APS_feature.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.7.8 交易频数滑窗统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:06:09.761496Z",
     "iopub.status.busy": "2023-11-07T04:06:09.761315Z",
     "iopub.status.idle": "2023-11-07T04:08:05.346781Z",
     "shell.execute_reply": "2023-11-07T04:08:05.346116Z",
     "shell.execute_reply.started": "2023-11-07T04:06:09.761474Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#时间滑窗交易频数统计：近2周/近4周/近6周/近8周的频数、间隔及与全量之比例\n",
    "APS_2 = APS_1_time_diff.copy(deep=True) \n",
    "\n",
    "APS_2_2 = APS_2.loc[APS_2['time_diff']>42]\n",
    "APS_2_2 = make_gp(APS_2_2, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "APS_2_2.columns = ['CUST_NO'] + [f +'_APS'+ '_last14' for f in APS_2_2.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "APS_2_4 = APS_2.loc[APS_2['time_diff']>28]\n",
    "APS_2_4 = make_gp(APS_2_4, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "APS_2_4.columns = ['CUST_NO'] + [f +'_APS' + '_last28' for f in APS_2_4.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "APS_2_6 = APS_2.loc[APS_2['time_diff']>14]\n",
    "APS_2_6 = make_gp(APS_2_6, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "APS_2_6.columns = ['CUST_NO'] + [f +'_APS'+ '_last42' for f in APS_2_6.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "APS_2_8 = APS_2.loc[APS_2['time_diff']>0]\n",
    "APS_2_8 = make_gp(APS_2_8, ['CUST_NO'], ['times', 'time_diff'], ['mean', 'sum', 'median', 'max', 'skew', 'std', pd.DataFrame.kurt])\n",
    "APS_2_8.columns = ['CUST_NO'] + [f +'_APS'+ '_last56' for f in APS_2_8.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "#汇总整理\n",
    "APS_2_huizong=pd.DataFrame()\n",
    "APS_2_huizong=APS_2_2.merge(APS_2_4, on = 'CUST_NO', how = 'left')\n",
    "APS_2_huizong=APS_2_huizong.merge(APS_2_6, on = 'CUST_NO', how = 'left')\n",
    "APS_2_huizong=APS_2_huizong.merge(APS_2_8, on = 'CUST_NO', how = 'left')\n",
    "for d in [14, 28,42,56]:\n",
    "        APS_2_huizong[f'times_APS_rate_last{d}'] = APS_2_huizong[f'times_sum_APS_last{d}'] / APS_1_huizong[f'APS_times_sum']\n",
    "        APS_2_huizong[f'time_APS_diff_rate_last{d}'] = APS_2_huizong[f'time_diff_sum_APS_last{d}'] / APS_1_huizong[f'APS_time_diff_sum']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.7.9 转出金额滑窗统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:08:05.347873Z",
     "iopub.status.busy": "2023-11-07T04:08:05.347682Z",
     "iopub.status.idle": "2023-11-07T04:08:18.643995Z",
     "shell.execute_reply": "2023-11-07T04:08:18.643348Z",
     "shell.execute_reply.started": "2023-11-07T04:08:05.347849Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#时间滑窗交易金额统计：：近2周/近4周/近6周/近8周的金额与与交易总金额之比\n",
    "APS_3 = tr_APS[tr_APS['APSDTRAMT']<0].copy(deep=True)\n",
    "APS_3.sort_values(by = ['CUST_NO', 'time_diff']).reset_index(drop = True)\n",
    "\n",
    "APS_3_2 = make_gp(APS_3[(APS_3['time_diff']>42)], ['CUST_NO',], ['APSDTRAMT',], ['mean', 'sum', 'max', 'min'])\n",
    "APS_3_2.columns = ['CUST_NO'] + [f +'_APSout'+ '_last14' for f in APS_3_2.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "APS_3_4 = make_gp(APS_3[(APS_3['time_diff']>28)], ['CUST_NO',], ['APSDTRAMT',], ['mean', 'sum', 'max', 'min'])\n",
    "APS_3_4.columns = ['CUST_NO'] + [f +'_APSout'+ '_last28' for f in APS_3_4.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "APS_3_6 = make_gp(APS_3[(APS_3['time_diff']>14)], ['CUST_NO',], ['APSDTRAMT',], ['mean', 'sum', 'max', 'min'])\n",
    "APS_3_6.columns = ['CUST_NO'] + [f +'_APSout'+ '_last42' for f in APS_3_6.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "APS_3_8 = make_gp(APS_3[(APS_3['time_diff']>0)], ['CUST_NO',], ['APSDTRAMT',], ['mean', 'sum', 'max', 'min'])\n",
    "APS_3_8.columns = ['CUST_NO'] + [f +'_APSout'+ '_last56' for f in APS_3_8.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "#汇总整理\n",
    "APS_3_huizong=pd.DataFrame()\n",
    "APS_3_huizong=APS_3_2.merge(APS_3_4, on = 'CUST_NO', how = 'left')\n",
    "APS_3_huizong=APS_3_huizong.merge(APS_3_6, on = 'CUST_NO', how = 'left')\n",
    "APS_3_huizong=APS_3_huizong.merge(APS_3_8, on = 'CUST_NO', how = 'left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 滑窗后的交易金额占比：（总—分）/总"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:08:18.645090Z",
     "iopub.status.busy": "2023-11-07T04:08:18.644895Z",
     "iopub.status.idle": "2023-11-07T04:08:18.748053Z",
     "shell.execute_reply": "2023-11-07T04:08:18.747527Z",
     "shell.execute_reply.started": "2023-11-07T04:08:18.645066Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "for col2 in ['APSDTRAMT', ]:\n",
    "    APS_3_huizong[f'out_{col2}_dr_sum_last28'] = (APS_am1_1[f'APS_outamt_{col2}_sum']- APS_3_huizong[f'{col2}_sum_APSout_last28'])/APS_3_huizong[f'{col2}_sum_APSout_last28']\n",
    "    APS_3_huizong[f'out_{col2}_dr_mean_last28'] = (APS_am1_1[f'APS_outamt_{col2}_mean']- APS_3_huizong[f'{col2}_mean_APSout_last28'])/APS_3_huizong[f'{col2}_mean_APSout_last28']\n",
    "    APS_3_huizong[f'out_{col2}_dr_max_last28']= (APS_am1_1[f'APS_outamt_{col2}_max']- APS_3_huizong[f'{col2}_max_APSout_last28'])/APS_3_huizong[f'{col2}_max_APSout_last28']                                                                                                              \n",
    "    APS_3_huizong[f'out_{col2}_dr_min_last28']= (APS_am1_1[f'APS_outamt_{col2}_min']- APS_3_huizong[f'{col2}_min_APSout_last28'])/APS_3_huizong[f'{col2}_min_APSout_last28']                                                                                                    \n",
    "    \n",
    "    APS_3_huizong[f'out_{col2}_dr_sum_last28'] = (APS_am1_1[f'APS_outamt_{col2}_sum']- APS_3_huizong[f'{col2}_sum_APSout_last28'])/APS_3_huizong[f'{col2}_sum_APSout_last28']\n",
    "    APS_3_huizong[f'out_{col2}_dr_mean_last28'] = (APS_am1_1[f'APS_outamt_{col2}_mean']- APS_3_huizong[f'{col2}_mean_APSout_last28'])/APS_3_huizong[f'{col2}_mean_APSout_last28']\n",
    "    APS_3_huizong[f'out_{col2}_dr_max_last28']= (APS_am1_1[f'APS_outamt_{col2}_max']- APS_3_huizong[f'{col2}_max_APSout_last28'])/APS_3_huizong[f'{col2}_max_APSout_last28']                                                                                                     \n",
    "    APS_3_huizong[f'out_{col2}_dr_min_last28']= (APS_am1_1[f'APS_outamt_{col2}_min']- APS_3_huizong[f'{col2}_min_APSout_last28'])/APS_3_huizong[f'{col2}_min_APSout_last28']\n",
    "    \n",
    "    APS_3_huizong[f'out_{col2}_dr_sum_last42'] = (APS_am1_1[f'APS_outamt_{col2}_sum']- APS_3_huizong[f'{col2}_sum_APSout_last42'])/APS_3_huizong[f'{col2}_sum_APSout_last42']\n",
    "    APS_3_huizong[f'out_{col2}_dr_mean_last42'] = (APS_am1_1[f'APS_outamt_{col2}_mean']- APS_3_huizong[f'{col2}_mean_APSout_last42'])/APS_3_huizong[f'{col2}_mean_APSout_last42']\n",
    "    APS_3_huizong[f'out_{col2}_dr_max_last42']= (APS_am1_1[f'APS_outamt_{col2}_max']- APS_3_huizong[f'{col2}_max_APSout_last42'])/APS_3_huizong[f'{col2}_max_APSout_last42']                                                                                                         \n",
    "    APS_3_huizong[f'out_{col2}_dr_min_last42']= (APS_am1_1[f'APS_outamt_{col2}_min']- APS_3_huizong[f'{col2}_min_APSout_last42'])/APS_3_huizong[f'{col2}_min_APSout_last42']\n",
    "    \n",
    "    APS_3_huizong[f'out_{col2}_dr_sum_last56'] = (APS_am1_1[f'APS_outamt_{col2}_sum']- APS_3_huizong[f'{col2}_sum_APSout_last56'])/APS_3_huizong[f'{col2}_sum_APSout_last56']\n",
    "    APS_3_huizong[f'out_{col2}_dr_mean_last56'] = (APS_am1_1[f'APS_outamt_{col2}_mean']- APS_3_huizong[f'{col2}_mean_APSout_last56'])/ APS_3_huizong[f'{col2}_mean_APSout_last56']\n",
    "    APS_3_huizong[f'out_{col2}_dr_max_last56']= (APS_am1_1[f'APS_outamt_{col2}_max']- APS_3_huizong[f'{col2}_max_APSout_last56'])/APS_3_huizong[f'{col2}_max_APSout_last56']                                                                                           \n",
    "    APS_3_huizong[f'out_{col2}_dr_min_last56']= (APS_am1_1[f'APS_outamt_{col2}_min']- APS_3_huizong[f'{col2}_min_APSout_last56'])/APS_3_huizong[f'{col2}_min_APSout_last56']\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.7.10 转入金额滑窗"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:08:18.749089Z",
     "iopub.status.busy": "2023-11-07T04:08:18.748902Z",
     "iopub.status.idle": "2023-11-07T04:08:26.806917Z",
     "shell.execute_reply": "2023-11-07T04:08:26.806274Z",
     "shell.execute_reply.started": "2023-11-07T04:08:18.749067Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#时间滑窗交易金额统计：：近2周/近4周/近6周/近8周的金额与与交易总金额之比\n",
    "APS_4 = tr_APS[tr_APS['APSDTRAMT']>0].copy(deep=True)\n",
    "APS_4.sort_values(by = ['CUST_NO', 'time_diff']).reset_index(drop = True)\n",
    "\n",
    "APS_4_2 = make_gp(APS_4[(APS_4['time_diff']>42)], ['CUST_NO',], ['APSDTRAMT',], ['mean', 'sum', 'max', 'min'])\n",
    "APS_4_2.columns = ['CUST_NO'] + [f +'_APSin'+ '_last14' for f in APS_4_2.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "APS_4_4 = make_gp(APS_4[(APS_4['time_diff']>28)], ['CUST_NO',], ['APSDTRAMT',], ['mean', 'sum', 'max', 'min'])\n",
    "APS_4_4.columns = ['CUST_NO'] + [f +'_APSin'+ '_last28' for f in APS_4_4.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "APS_4_6 = make_gp(APS_4[(APS_4['time_diff']>14)], ['CUST_NO',], ['APSDTRAMT',], ['mean', 'sum', 'max', 'min'])\n",
    "APS_4_6.columns = ['CUST_NO'] + [f +'_APSin'+ '_last42' for f in APS_4_6.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "APS_4_8 = make_gp(APS_4[(APS_4['time_diff']>0)], ['CUST_NO',], ['APSDTRAMT',], ['mean', 'sum', 'max', 'min'])\n",
    "APS_4_8.columns = ['CUST_NO'] + [f +'_APSin'+ '_last56' for f in APS_4_8.columns if f not in ['CUST_NO',]]\n",
    "\n",
    "#汇总整理\n",
    "APS_4_huizong=pd.DataFrame()\n",
    "APS_4_huizong=APS_4_2.merge(APS_4_4, on = 'CUST_NO', how = 'left')\n",
    "APS_4_huizong=APS_4_huizong.merge(APS_4_6, on = 'CUST_NO', how = 'left')\n",
    "APS_4_huizong=APS_4_huizong.merge(APS_4_8, on = 'CUST_NO', how = 'left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 滑窗后的交易金额占比：（总—分）/总"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:08:26.807996Z",
     "iopub.status.busy": "2023-11-07T04:08:26.807803Z",
     "iopub.status.idle": "2023-11-07T04:08:26.913454Z",
     "shell.execute_reply": "2023-11-07T04:08:26.912896Z",
     "shell.execute_reply.started": "2023-11-07T04:08:26.807972Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "for col2 in ['APSDTRAMT', ]:\n",
    "    APS_4_huizong[f'in_{col2}_dr_sum_last28'] = (APS_am2_1[f'APS_inamt_{col2}_sum']- APS_4_huizong[f'{col2}_sum_APSin_last28'])/APS_4_huizong[f'{col2}_sum_APSin_last28']\n",
    "    APS_4_huizong[f'in_{col2}_dr_mean_last28'] = (APS_am2_1[f'APS_inamt_{col2}_mean']- APS_4_huizong[f'{col2}_mean_APSin_last28'])/APS_4_huizong[f'{col2}_mean_APSin_last28']\n",
    "    APS_4_huizong[f'in_{col2}_dr_max_last28']= (APS_am2_1[f'APS_inamt_{col2}_max']- APS_4_huizong[f'{col2}_max_APSin_last28'])/APS_4_huizong[f'{col2}_max_APSin_last28']                                                                                                           \n",
    "    APS_4_huizong[f'in_{col2}_dr_min_last28']= (APS_am2_1[f'APS_inamt_{col2}_min']- APS_4_huizong[f'{col2}_min_APSin_last28'])/APS_4_huizong[f'{col2}_min_APSin_last28']                                                                                                          \n",
    "    \n",
    "    APS_4_huizong[f'in_{col2}_dr_sum_last28'] = (APS_am2_1[f'APS_inamt_{col2}_sum']- APS_4_huizong[f'{col2}_sum_APSin_last28'])/APS_4_huizong[f'{col2}_sum_APSin_last28']\n",
    "    APS_4_huizong[f'in_{col2}_dr_mean_last28'] = (APS_am2_1[f'APS_inamt_{col2}_mean']- APS_4_huizong[f'{col2}_mean_APSin_last28'])/APS_4_huizong[f'{col2}_mean_APSin_last28']\n",
    "    APS_4_huizong[f'in_{col2}_dr_max_last28']= (APS_am2_1[f'APS_inamt_{col2}_max']- APS_4_huizong[f'{col2}_max_APSin_last28'])/APS_4_huizong[f'{col2}_max_APSin_last28']                                                                                                      \n",
    "    APS_4_huizong[f'in_{col2}_dr_min_last28']= (APS_am2_1[f'APS_inamt_{col2}_min']- APS_4_huizong[f'{col2}_min_APSin_last28'])/APS_4_huizong[f'{col2}_min_APSin_last28']\n",
    "    \n",
    "    APS_4_huizong[f'in_{col2}_dr_sum_last42'] = (APS_am2_1[f'APS_inamt_{col2}_sum']- APS_4_huizong[f'{col2}_sum_APSin_last42'])/APS_4_huizong[f'{col2}_sum_APSin_last42']\n",
    "    APS_4_huizong[f'in_{col2}_dr_mean_last42'] = (APS_am2_1[f'APS_inamt_{col2}_mean']- APS_4_huizong[f'{col2}_mean_APSin_last42'])/APS_4_huizong[f'{col2}_mean_APSin_last42']\n",
    "    APS_4_huizong[f'in_{col2}_dr_max_last42']= (APS_am2_1[f'APS_inamt_{col2}_max']- APS_4_huizong[f'{col2}_max_APSin_last42'])/APS_4_huizong[f'{col2}_max_APSin_last42']                                                                                                       \n",
    "    APS_4_huizong[f'in_{col2}_dr_min_last42']= (APS_am2_1[f'APS_inamt_{col2}_min']- APS_4_huizong[f'{col2}_min_APSin_last42'])/APS_4_huizong[f'{col2}_min_APSin_last42']\n",
    "    \n",
    "    APS_4_huizong[f'in_{col2}_dr_sum_last56'] = (APS_am2_1[f'APS_inamt_{col2}_sum']- APS_4_huizong[f'{col2}_sum_APSin_last56'])/APS_4_huizong[f'{col2}_sum_APSin_last56']\n",
    "    APS_4_huizong[f'in_{col2}_dr_mean_last56'] = (APS_am2_1[f'APS_inamt_{col2}_mean']- APS_4_huizong[f'{col2}_mean_APSin_last56'])/APS_4_huizong[f'{col2}_mean_APSin_last56']\n",
    "    APS_4_huizong[f'in_{col2}_dr_max_last56']= (APS_am2_1[f'APS_inamt_{col2}_max']- APS_4_huizong[f'{col2}_max_APSin_last56'])/APS_4_huizong[f'{col2}_max_APSin_last56']                                                                                               \n",
    "    APS_4_huizong[f'in_{col2}_dr_min_last56']= (APS_am2_1[f'APS_inamt_{col2}_min']- APS_4_huizong[f'{col2}_min_APSin_last56'])/APS_4_huizong[f'{col2}_min_APSin_last56']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.7.11 汇总所有的滑窗结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:08:26.914523Z",
     "iopub.status.busy": "2023-11-07T04:08:26.914334Z",
     "iopub.status.idle": "2023-11-07T04:08:28.136911Z",
     "shell.execute_reply": "2023-11-07T04:08:28.136416Z",
     "shell.execute_reply.started": "2023-11-07T04:08:26.914500Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(42016, 267)"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "APS_huachuang_huizong=pd.DataFrame()\n",
    "APS_huachuang_huizong=APS_2_huizong.merge(APS_3_huizong, on = 'CUST_NO', how = 'left')\n",
    "APS_huachuang_huizong=APS_huachuang_huizong.merge(APS_4_huizong, on = 'CUST_NO', how = 'left') \n",
    "\n",
    "# 空值率高\n",
    "null_percentage = APS_huachuang_huizong.isnull().mean()\n",
    "threshold = 0.95\n",
    "null_high_col_aps2 = []\n",
    "null_high_col_aps2 = null_percentage[null_percentage>threshold].index.tolist()\n",
    "\n",
    "APS_feature=APS_feature.merge(APS_huachuang_huizong, on = 'CUST_NO', how = 'left')\n",
    "APS_feature.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.7.12 保存APS_feature特征结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:08:28.138017Z",
     "iopub.status.busy": "2023-11-07T04:08:28.137826Z",
     "iopub.status.idle": "2023-11-07T04:08:28.441574Z",
     "shell.execute_reply": "2023-11-07T04:08:28.441000Z",
     "shell.execute_reply.started": "2023-11-07T04:08:28.137993Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pickle\n",
    "\n",
    "# 指定要保存的文件名\n",
    "file_name = \"./feature/B_5_APS.pkl\"\n",
    "\n",
    "# 使用pickle.dump()将特征矩阵保存为二进制文件\n",
    "with open(file_name, 'wb') as file:\n",
    "    pickle.dump(APS_feature, file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 4 分组统计特征补充"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 函数定义"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:08:28.442701Z",
     "iopub.status.busy": "2023-11-07T04:08:28.442511Z",
     "iopub.status.idle": "2023-11-07T04:08:28.453459Z",
     "shell.execute_reply": "2023-11-07T04:08:28.452897Z",
     "shell.execute_reply.started": "2023-11-07T04:08:28.442677Z"
    }
   },
   "outputs": [],
   "source": [
    "def common_process(df, id, op_cols, pks=None, col_type=None, ext=''):\n",
    "    def kurt(arr):\n",
    "        return arr.kurt()\n",
    "    # 统计sliding_window\n",
    "    # stat_name = ['mean','max','min','median','std','sum','skew','count','nunique','last', 'ptp','kurt']\n",
    "    # stat_features = ['mean','max','min','median','std','sum','skew','count','nunique','last',np.ptp, kurt]\n",
    "    stat_name = ['mean','max','min','median','std','sum','count']\n",
    "    stat_features = ['mean','max','min','median','std','sum','count']\n",
    "    if col_type != 'num':\n",
    "        stat_name = ['nunique','count']\n",
    "        stat_features = ['nunique','count'] # count 容易重复\n",
    "    agg_stat = {}\n",
    "    # 存放统计列，为后面行转列准备\n",
    "    temp_columns=[]\n",
    "    for i in op_cols:\n",
    "        agg_stat[i] = stat_features\n",
    "        temp_columns.extend([i + '_' + f + '_' + ext for f in stat_name])\n",
    "\n",
    "    ids = [id]\n",
    "    if pks is not None:\n",
    "        ids.extend(pks)\n",
    "    temp = df.groupby(ids).agg(agg_stat)\n",
    "    temp.columns = [f[0]+ '_' + f[1]  + \"_\" + str(ext) for f in temp.columns]\n",
    "    # print(temp.columns)\n",
    "    temp = temp.reset_index(drop = False)\n",
    "\n",
    "    if pks is not None:\n",
    "        # 将主键列合并成一列，后面扩展为列名\n",
    "        temp['combine'] = temp[pks[0]]\n",
    "        for i in pks[1:]:\n",
    "            temp['combine'] = temp['combine'].astype(str) +\"_\"+ temp[i].astype(str)\n",
    "        # 行转列，类别转列名特征\n",
    "        # id,扩展列，统计量的列\n",
    "#         print(temp_columns)\n",
    "#         print(temp.head())\n",
    "        temp=temp.set_index([id,'combine'])[temp_columns].unstack()\n",
    "        temp.columns = [str(f[0])+ '_' + str(f[1]) for f in temp.columns]\n",
    "        temp=temp.reset_index()\n",
    "    return temp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:08:28.454370Z",
     "iopub.status.busy": "2023-11-07T04:08:28.454197Z",
     "iopub.status.idle": "2023-11-07T04:08:28.457852Z",
     "shell.execute_reply": "2023-11-07T04:08:28.457384Z",
     "shell.execute_reply.started": "2023-11-07T04:08:28.454349Z"
    }
   },
   "outputs": [],
   "source": [
    "#剔除缺失值\n",
    "def res(df):\n",
    "    res = stat_df(df)\n",
    "    res = res[res['缺失值占比'] > 80]\n",
    "    missing = np.array(res['特征'])\n",
    "    print(np.shape(missing))\n",
    "    return missing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:08:28.458728Z",
     "iopub.status.busy": "2023-11-07T04:08:28.458558Z",
     "iopub.status.idle": "2023-11-07T04:08:28.461962Z",
     "shell.execute_reply": "2023-11-07T04:08:28.461503Z",
     "shell.execute_reply.started": "2023-11-07T04:08:28.458706Z"
    }
   },
   "outputs": [],
   "source": [
    "#最终特征确认\n",
    "def feature_qr(df,missing):\n",
    "    feature_name = [i for i in df.columns if i not in missing]\n",
    "    df = df[feature_name].reset_index(drop=True)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:08:28.462902Z",
     "iopub.status.busy": "2023-11-07T04:08:28.462726Z",
     "iopub.status.idle": "2023-11-07T04:09:17.967468Z",
     "shell.execute_reply": "2023-11-07T04:09:17.966959Z",
     "shell.execute_reply.started": "2023-11-07T04:08:28.462880Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1379,)\n",
      "(3038,)\n",
      "(138,)\n",
      "(394,)\n",
      "(868,)\n",
      "(138,)\n",
      "(394,)\n",
      "(868,)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(41287, 253)"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#1 掌银金融性流水表\n",
    "MBANK_TRNFLW_data['TFT_DTE_week'] = MBANK_TRNFLW_data['TFT_DTE_TIME'].dt.dayofweek\n",
    "col=['TFT_DTE_TIME','TFT_DTE_year','TFT_DTE_day']\n",
    "for i in col:\n",
    "    del MBANK_TRNFLW_data[i]\n",
    "\n",
    "\n",
    "# 根据账号和 标准业务代码按月分组统计交易金额\n",
    "tr_mamt_TRNFLW= common_process(MBANK_TRNFLW_data,'CUST_NO',['TFT_TRNAMT'],['TFT_DTE_month','TFT_STDBSNCOD'],col_type='num',ext='mbank_trn_mamt')\n",
    "missing=res(tr_mamt_TRNFLW)\n",
    "tr_mamt_TRNFLW=feature_qr(tr_mamt_TRNFLW,missing)\n",
    "\n",
    "# 根据账号和 标准业务代码按周分组统计交易金额\n",
    "tr_wamt_TRNFLW= common_process(MBANK_TRNFLW_data,'CUST_NO',['TFT_TRNAMT'],['TFT_DTE_week','TFT_STDBSNCOD'],col_type='num',ext='mbank_trn_wamt')\n",
    "missing=res(tr_wamt_TRNFLW)\n",
    "tr_wamt_TRNFLW=feature_qr(tr_wamt_TRNFLW,missing)\n",
    "\n",
    "# 根据账号和 标准业务代码按年分组统计交易频数\n",
    "tr_ycnt_TRNFLW= common_process(MBANK_TRNFLW_data,'CUST_NO',['TFT_CSTACC'],['TFT_STDBSNCOD'],col_type='',ext='mbank_trn_ycnt')\n",
    "missing=res(tr_ycnt_TRNFLW)\n",
    "tr_ycnt_TRNFLW=feature_qr(tr_ycnt_TRNFLW,missing)\n",
    "\n",
    "# 根据账号和 标准业务代码按月分组统计交易频数\n",
    "tr_mcnt_TRNFLW= common_process(MBANK_TRNFLW_data,'CUST_NO',['TFT_CSTACC'],['TFT_DTE_month','TFT_STDBSNCOD'],col_type='',ext='mbank_trn_mcnt')\n",
    "missing=res(tr_mcnt_TRNFLW)\n",
    "tr_mcnt_TRNFLW=feature_qr(tr_mcnt_TRNFLW,missing)\n",
    "\n",
    "#根据客户号和标准业务代码按周分组统计交易频数\n",
    "tr_wcnt_TRNFLW= common_process(MBANK_TRNFLW_data,'CUST_NO',['TFT_CSTACC'],['TFT_DTE_week','TFT_STDBSNCOD'],col_type='',ext='mbank_trn_wcnt')\n",
    "missing=res(tr_wcnt_TRNFLW)\n",
    "tr_wcnt_TRNFLW=feature_qr(tr_wcnt_TRNFLW,missing)\n",
    "\n",
    "#根据客户号和标准业务代码按年分组统计交易对手\n",
    "tr_yacccnt_TRNFLW= common_process(MBANK_TRNFLW_data,'CUST_NO',['TFT_CRPACC'],['TFT_STDBSNCOD'],col_type='',ext='mbank_trn_yacccnt')\n",
    "missing=res(tr_yacccnt_TRNFLW)\n",
    "tr_wcnt_TRNFLW=feature_qr(tr_yacccnt_TRNFLW,missing)\n",
    "\n",
    "#根据客户号和标准业务代码按月分组统计交易对手\n",
    "tr_macccnt_TRNFLW= common_process(MBANK_TRNFLW_data,'CUST_NO',['TFT_CRPACC'],['TFT_DTE_month','TFT_STDBSNCOD'],col_type='',ext='mbank_trn_macccnt')\n",
    "missing=res(tr_macccnt_TRNFLW)\n",
    "tr_macccnt_TRNFLW=feature_qr(tr_macccnt_TRNFLW,missing)\n",
    "\n",
    "#根据客户号和标准业务代码按周分组统计交易对手\n",
    "tr_wacccnt_TRNFLW= common_process(MBANK_TRNFLW_data,'CUST_NO',['TFT_CRPACC'],['TFT_DTE_week','TFT_STDBSNCOD'],col_type='',ext='mbank_trn_wacccnt')\n",
    "missing=res(tr_wacccnt_TRNFLW)\n",
    "tr_wacccnt_TRNFLW=feature_qr(tr_wacccnt_TRNFLW,missing)\n",
    "\n",
    "#合并\n",
    "TRNFLW_feature1=pd.DataFrame(MBANK_TRNFLW_data[\"CUST_NO\"].drop_duplicates())\n",
    "\n",
    "TRNFLW_feature1=TRNFLW_feature1.merge(tr_mamt_TRNFLW, on = 'CUST_NO', how = 'left')\n",
    "TRNFLW_feature1=TRNFLW_feature1.merge(tr_wamt_TRNFLW, on = 'CUST_NO', how = 'left')\n",
    "TRNFLW_feature1=TRNFLW_feature1.merge(tr_ycnt_TRNFLW, on = 'CUST_NO', how = 'left')\n",
    "TRNFLW_feature1=TRNFLW_feature1.merge(tr_mcnt_TRNFLW, on = 'CUST_NO', how = 'left')\n",
    "TRNFLW_feature1=TRNFLW_feature1.merge(tr_wcnt_TRNFLW, on = 'CUST_NO', how = 'left')\n",
    "TRNFLW_feature1=TRNFLW_feature1.merge(tr_yacccnt_TRNFLW, on = 'CUST_NO', how = 'left')\n",
    "TRNFLW_feature1=TRNFLW_feature1.merge(tr_macccnt_TRNFLW, on = 'CUST_NO', how = 'left')\n",
    "TRNFLW_feature1=TRNFLW_feature1.merge(tr_wacccnt_TRNFLW, on = 'CUST_NO', how = 'left')\n",
    "\n",
    "TRNFLW_feature1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:09:17.968616Z",
     "iopub.status.busy": "2023-11-07T04:09:17.968414Z",
     "iopub.status.idle": "2023-11-07T04:10:11.804118Z",
     "shell.execute_reply": "2023-11-07T04:10:11.803616Z",
     "shell.execute_reply.started": "2023-11-07T04:09:17.968591Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(436,)\n",
      "(1194,)\n",
      "(2604,)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(41945, 99)"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#2 掌银非金融\n",
    "MBANK_QRYTRNFLW_data['TFT_DTE_week'] = MBANK_QRYTRNFLW_data['TFT_DTE_TIME'].dt.dayofweek\n",
    "col=['TFT_DTE_TIME','TFT_DTE_year','TFT_DTE_day']\n",
    "for i in col:\n",
    "    del MBANK_QRYTRNFLW_data[i]\n",
    "\n",
    "# 根据账号和 标准业务代码按年分组统计交易频数\n",
    "tr_ycnt_QRYTRNFLW= common_process(MBANK_QRYTRNFLW_data,'CUST_NO',['CUST_NO'],['TFT_STDBSNCOD'],col_type='',ext='mbank_qry_ycnt')\n",
    "missing=res(tr_ycnt_QRYTRNFLW)\n",
    "tr_ycnt_QRYTRNFLW=feature_qr(tr_ycnt_QRYTRNFLW,missing)\n",
    "\n",
    "#根据客户号和标准业务代码按月分组统计交易频数\n",
    "tr_mcnt_QRYTRNFLW= common_process(MBANK_QRYTRNFLW_data,'CUST_NO',['CUST_NO'],['TFT_DTE_month','TFT_STDBSNCOD'],col_type='',ext='mbank_qry_mcnt')\n",
    "missing=res(tr_mcnt_QRYTRNFLW)\n",
    "tr_mcnt_QRYTRNFLW=feature_qr(tr_mcnt_QRYTRNFLW,missing)\n",
    "\n",
    "#根据客户号和标准业务代码按周分组统计交易频数\n",
    "tr_wcnt_QRYTRNFLW= common_process(MBANK_QRYTRNFLW_data,'CUST_NO',['CUST_NO'],['TFT_DTE_week','TFT_STDBSNCOD'],col_type='',ext='mbank_qry_wcnt')\n",
    "missing=res(tr_wcnt_QRYTRNFLW)\n",
    "tr_wcnt_QRYTRNFLW=feature_qr(tr_wcnt_QRYTRNFLW,missing)\n",
    "# 合并\n",
    "QRYTRNFLW_feature1=pd.DataFrame(MBANK_QRYTRNFLW_data[\"CUST_NO\"].drop_duplicates())\n",
    "QRYTRNFLW_feature1=QRYTRNFLW_feature1.merge(tr_ycnt_QRYTRNFLW, on = 'CUST_NO', how = 'left')\n",
    "QRYTRNFLW_feature1=QRYTRNFLW_feature1.merge(tr_mcnt_QRYTRNFLW, on = 'CUST_NO', how = 'left')\n",
    "QRYTRNFLW_feature1=QRYTRNFLW_feature1.merge(tr_wcnt_QRYTRNFLW, on = 'CUST_NO', how = 'left')\n",
    "QRYTRNFLW_feature1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:10:11.805262Z",
     "iopub.status.busy": "2023-11-07T04:10:11.805068Z",
     "iopub.status.idle": "2023-11-07T04:10:13.330924Z",
     "shell.execute_reply": "2023-11-07T04:10:13.330416Z",
     "shell.execute_reply.started": "2023-11-07T04:10:11.805237Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(57,)\n",
      "(161,)\n",
      "(365,)\n",
      "(16,)\n",
      "(46,)\n",
      "(104,)\n",
      "(16,)\n",
      "(46,)\n",
      "(104,)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(490, 131)"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 3网银金融\n",
    "EBANK_CSTLOG_data['ADDFIELDDATE_week'] = EBANK_CSTLOG_data['ADDFIELDDATE'].dt.dayofweek\n",
    "col=['ADDFIELDDATE','ADDFIELDDATE_year','ADDFIELDDATE_day']\n",
    "for i in col:\n",
    "    del EBANK_CSTLOG_data[i]\n",
    "\n",
    "#根据客户号和标准业务代码按年分组统计交易金额\n",
    "tr_yamt_CSTLOG= common_process(EBANK_CSTLOG_data,'CUST_NO',['TRNAMT'],['BSNCODE'],col_type='num',ext='ebank_trn_yamt')\n",
    "missing=res(tr_yamt_CSTLOG)\n",
    "tr_yamt_CSTLOG=feature_qr(tr_yamt_CSTLOG,missing)\n",
    "\n",
    "#根据客户号和标准业务代码按月分组统计交易金额\n",
    "tr_mamt_CSTLOG= common_process(EBANK_CSTLOG_data,'CUST_NO',['TRNAMT'],['ADDFIELDDATE_month','BSNCODE'],col_type='num',ext='ebank_trn_mamt')\n",
    "missing=res(tr_mamt_CSTLOG)\n",
    "tr_mamt_CSTLOG=feature_qr(tr_mamt_CSTLOG,missing)\n",
    "\n",
    "#根据客户号和标准业务代码按周分组统计交易金额\n",
    "tr_wamt_CSTLOG= common_process(EBANK_CSTLOG_data,'CUST_NO',['TRNAMT'],['ADDFIELDDATE_week','BSNCODE'],col_type='num',ext='ebank_trn_wamt')\n",
    "missing=res(tr_wamt_CSTLOG)\n",
    "tr_wamt_CSTLOG=feature_qr(tr_wamt_CSTLOG,missing)\n",
    "\n",
    "#根据客户号和标准业务代码按年分组统计交易频数\n",
    "tr_ycnt_CSTLOG= common_process(EBANK_CSTLOG_data,'CUST_NO',['FRMACCTNO'],['BSNCODE'],col_type='',ext='ebank_trn_ycnt')\n",
    "missing=res(tr_ycnt_CSTLOG)\n",
    "tr_ycnt_CSTLOG=feature_qr(tr_ycnt_CSTLOG,missing)\n",
    "\n",
    "#根据客户号和标准业务代码按月分组统计交易频数\n",
    "tr_mcnt_CSTLOG= common_process(EBANK_CSTLOG_data,'CUST_NO',['FRMACCTNO'],['ADDFIELDDATE_month','BSNCODE'],col_type='',ext='ebank_trn_mcnt')\n",
    "missing=res(tr_mcnt_CSTLOG)\n",
    "tr_mcnt_CSTLOG=feature_qr(tr_mcnt_CSTLOG,missing)\n",
    "tr_mcnt_CSTLOG.shape\n",
    "\n",
    "#根据客户号和标准业务代码按周分组统计交易频数\n",
    "tr_wcnt_CSTLOG= common_process(EBANK_CSTLOG_data,'CUST_NO',['FRMACCTNO'],['ADDFIELDDATE_week','BSNCODE'],col_type='',ext='ebank_trn_wcnt')\n",
    "missing=res(tr_wcnt_CSTLOG)\n",
    "tr_wcnt_CSTLOG=feature_qr(tr_wcnt_CSTLOG,missing)\n",
    "\n",
    "#根据客户号和标准业务代码按年分组统计交易对手\n",
    "tr_yacccnt_CSTLOG= common_process(EBANK_CSTLOG_data,'CUST_NO',['TOACCTNO'],['BSNCODE'],col_type='',ext='ebank_trn_yacccnt')\n",
    "missing=res(tr_yacccnt_CSTLOG)\n",
    "tr_yacccnt_CSTLOG=feature_qr(tr_yacccnt_CSTLOG,missing)\n",
    "\n",
    "#根据客户号和标准业务代码按月分组统计交易对手\n",
    "tr_macccnt_CSTLOG= common_process(EBANK_CSTLOG_data,'CUST_NO',['TOACCTNO'],['ADDFIELDDATE_month','BSNCODE'],col_type='',ext='ebank_trn_macccnt')\n",
    "missing=res(tr_macccnt_CSTLOG)\n",
    "tr_macccnt_CSTLOG=feature_qr(tr_macccnt_CSTLOG,missing)\n",
    "\n",
    "#根据客户号和标准业务代码按周分组统计交易对手\n",
    "tr_wacccnt_CSTLOG= common_process(EBANK_CSTLOG_data,'CUST_NO',['TOACCTNO'],['ADDFIELDDATE_week','BSNCODE'],col_type='',ext='ebank_trn_wacccnt')\n",
    "missing=res(tr_wacccnt_CSTLOG)\n",
    "tr_wacccnt_CSTLOG=feature_qr(tr_wacccnt_CSTLOG,missing)\n",
    "\n",
    "# 合并\n",
    "CSTLOG_feature1=pd.DataFrame(EBANK_CSTLOG_data[\"CUST_NO\"].drop_duplicates())\n",
    "\n",
    "CSTLOG_feature1=CSTLOG_feature1.merge(tr_yamt_CSTLOG, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_feature1=CSTLOG_feature1.merge(tr_mamt_CSTLOG, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_feature1=CSTLOG_feature1.merge(tr_wamt_CSTLOG, on = 'CUST_NO', how = 'left')\n",
    "\n",
    "CSTLOG_feature1=CSTLOG_feature1.merge(tr_ycnt_CSTLOG, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_feature1=CSTLOG_feature1.merge(tr_mcnt_CSTLOG, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_feature1=CSTLOG_feature1.merge(tr_wcnt_CSTLOG, on = 'CUST_NO', how = 'left')\n",
    "\n",
    "CSTLOG_feature1=CSTLOG_feature1.merge(tr_yacccnt_CSTLOG, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_feature1=CSTLOG_feature1.merge(tr_macccnt_CSTLOG, on = 'CUST_NO', how = 'left')\n",
    "CSTLOG_feature1=CSTLOG_feature1.merge(tr_wacccnt_CSTLOG, on = 'CUST_NO', how = 'left')\n",
    "\n",
    "CSTLOG_feature1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:10:13.332128Z",
     "iopub.status.busy": "2023-11-07T04:10:13.331936Z",
     "iopub.status.idle": "2023-11-07T04:10:27.288298Z",
     "shell.execute_reply": "2023-11-07T04:10:27.287790Z",
     "shell.execute_reply.started": "2023-11-07T04:10:13.332104Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(374,)\n",
      "(786,)\n",
      "(1520,)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(1082, 387)"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 4网银非金融\n",
    "EBANK_CSTLOGQUERY_data['CLQ_DTE_TIME_week'] = EBANK_CSTLOGQUERY_data['CLQ_DTE_TIME'].dt.dayofweek\n",
    "col=['CLQ_DTE_TIME','CLQ_DTE_TIME_year','CLQ_DTE_day']\n",
    "for i in col:\n",
    "    del EBANK_CSTLOGQUERY_data[i]\n",
    "\n",
    "#根据客户号和标准业务代码按年分组统计交易频数\n",
    "tr_ycnt_CSTLOGQUERY= common_process(EBANK_CSTLOGQUERY_data,'CUST_NO',['CUST_NO'],['CLQ_BSNCOD'],col_type='',ext='ebank_qry_ycnt')\n",
    "missing=res(tr_ycnt_CSTLOGQUERY)\n",
    "tr_ycnt_CSTLOGQUERY=feature_qr(tr_ycnt_CSTLOGQUERY,missing)\n",
    "\n",
    "#根据客户号和标准业务代码月年分组统计交易频数\n",
    "tr_mcnt_CSTLOGQUERY= common_process(EBANK_CSTLOGQUERY_data,'CUST_NO',['CUST_NO'],['CLQ_DTE_TIME_month','CLQ_BSNCOD'],col_type='',ext='ebank_qry_mcnt')\n",
    "missing=res(tr_mcnt_CSTLOGQUERY)\n",
    "tr_mcnt_CSTLOGQUERY=feature_qr(tr_mcnt_CSTLOGQUERY,missing)\n",
    "\n",
    "#根据客户号和标准业务代码按周分组统计交易频数\n",
    "tr_wcnt_CSTLOGQUERY= common_process(EBANK_CSTLOGQUERY_data,'CUST_NO',['CUST_NO'],['CLQ_DTE_TIME_week','CLQ_BSNCOD'],col_type='',ext='ebank_qry_wcnt')\n",
    "missing=res(tr_wcnt_CSTLOGQUERY)\n",
    "tr_wcnt_CSTLOGQUERY=feature_qr(tr_wcnt_CSTLOGQUERY,missing)\n",
    "\n",
    "#合并\n",
    "CSTLOGQUERY_feature1=pd.DataFrame(EBANK_CSTLOGQUERY_data[\"CUST_NO\"].drop_duplicates())\n",
    "CSTLOGQUERY_feature1=CSTLOGQUERY_feature1.merge(tr_ycnt_CSTLOGQUERY, on = 'CUST_NO', how = 'left')\n",
    "CSTLOGQUERY_feature1=CSTLOGQUERY_feature1.merge(tr_mcnt_CSTLOGQUERY, on = 'CUST_NO', how = 'left')\n",
    "CSTLOGQUERY_feature1=CSTLOGQUERY_feature1.merge(tr_wcnt_CSTLOGQUERY, on = 'CUST_NO', how = 'left')\n",
    "CSTLOGQUERY_feature1.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 合并结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:10:27.289371Z",
     "iopub.status.busy": "2023-11-07T04:10:27.289186Z",
     "iopub.status.idle": "2023-11-07T04:10:32.335434Z",
     "shell.execute_reply": "2023-11-07T04:10:32.334922Z",
     "shell.execute_reply.started": "2023-11-07T04:10:27.289347Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(654,)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(42139, 213)"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feature_1234=pd.DataFrame(target_data[\"CUST_NO\"].drop_duplicates())\n",
    "feature_1234=feature_1234.merge(TRNFLW_feature1, on = 'CUST_NO', how = 'left')\n",
    "feature_1234=feature_1234.merge(QRYTRNFLW_feature1, on = 'CUST_NO', how = 'left')\n",
    "feature_1234=feature_1234.merge(CSTLOG_feature1, on = 'CUST_NO', how = 'left')\n",
    "feature_1234=feature_1234.merge(CSTLOGQUERY_feature1, on = 'CUST_NO', how = 'left')\n",
    "feature_1234.shape\n",
    "missing=res(feature_1234)\n",
    "feature_1234=feature_qr(feature_1234,missing)\n",
    "feature_1234.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T04:10:32.336564Z",
     "iopub.status.busy": "2023-11-07T04:10:32.336368Z",
     "iopub.status.idle": "2023-11-07T04:10:32.606503Z",
     "shell.execute_reply": "2023-11-07T04:10:32.605825Z",
     "shell.execute_reply.started": "2023-11-07T04:10:32.336540Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pickle\n",
    "\n",
    "# 指定要保存的文件名\n",
    "file_name = \"./feature/B_1234_hebing.pkl\"\n",
    "\n",
    "# 使用pickle.dump()将特征矩阵保存为二进制文件\n",
    "with open(file_name, 'wb') as file:\n",
    "    pickle.dump(feature_1234, file)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
